Dummy FAQ of the day - outputting parameters

Question

Post reply

Dummy FAQ of the day - outputting parameters

jvoelcker

Old Hand

Points: 342
More actions
May 16, 2006 at 6:34 pm

#117570

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Check out Output parameters in BOL.

_____________
Code for TallyGenerator

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jvoelcker Old Hand Points: 342 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vadba SSChampion Points: 11133 More actions · Answer 5

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.

Dan Murray Right there with Babe Points: 779 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jvoelcker Old Hand Points: 342 More actions · Answer 8

Thanks one and all, have worked around the problem.

Cheers,

Julian