May 16, 2006 at 6:34 pm
OK here's the dummy question of the day....
I want to write a sproc to run a query based on an inputted value and then output a single value.
The value is coming from an NTEXT field and if the query doesn't return any values I want to just output nothing ("") or a null value.
I've used RETURN to return values in the past, but understand that it only handles integers, not text or ntext values.
Any pointers would be appreciated.
Thanks in advance.
Cheers,
Julian
May 16, 2006 at 8:06 pm
Check out Output parameters in BOL.
_____________
Code for TallyGenerator
May 17, 2006 at 2:00 am
In theory
CREATE PROCEDURE TestOutput (
@InputParameter INT
@OutputParameter INT OUTPUT
)
AS
.....
DECLARE @Result INT
EXEC TestOutput @InputParameter = 1, @OutputParameter = @Result OUTPUT
Note the OUTPUT keyword, both when creating the stored proc and wne calling it. Omit either and you won't set anything out. Within the procedure you can treat the output parameter(s) exacly as you would normal parameters or variables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2006 at 2:04 am
I've been looking through it but am struggling with the fact that I am trying to extract ntext.
So far I have:
ALTER PROCEDURE dbo.proc_Templates_GetTemplateByObjectPath
(
@inputObjectPath varchar(100), @myTemplate NTEXT output
)
AS
SET NOCOUNT OFF
/* logic to get @TempID */
SELECT @myTemplate = TemplateContent FROM Templates WHERE TemplateID = @TempID
RETURN
GO
Which follows the logic that the book identifies, but I get the error:
The assignment operator operation cannot take an ntext data type as an argument
Any ideas?
Cheers,
Julian
May 17, 2006 at 2:30 am
The error has more to do with the wierd way text fields behave than anything wrong with your parameters. You might have some success using READTEXT, but I don't really know. I don't work much with text fields.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 17, 2006 at 6:35 am
Although you can declare store procedures with text and ntext parameters, they cannot be accessed or used with T-SQL.
Your only choice is to convert the ntext to nvarchar(4000), or place the results in a table that the caller can access.
May 17, 2006 at 7:46 am
If you search the Sql Help documentation you'll probably find this warning.
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types.
Beware
May 17, 2006 at 7:54 am
However, if you're still using SQL 2000, then the max datatypes are not available (New feature in 2005) and you're stuck with TEXT for large text strings.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 18, 2006 at 1:58 am
Thanks one and all, have worked around the problem.
Cheers,
Julian
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply