Text variable as stored proc output

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Text variable as stored proc output

Author

Topic

MuppetCoder
Starting Member

6 Posts

Posted - 2004-08-25 : 05:32:23

It seems to be possible to set a text data type parameter for a stored proc as output - is there any way to set that in the stored proc? Obviously you cannot create a local variable as type text.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-25 : 08:34:55

you could return a record set with a text column? but thats all that I can come up with...

Corey

Daveyboy69
Starting Member

11 Posts

Posted - 2004-09-03 : 08:16:03

There is, see this example from books online:

CREATE PROCEDURE get_sales_for_title
@title varchar(80), -- This is the input parameter.
@ytd_sales int OUTPUT -- This is the output parameter.
AS

-- Get the sales for the specified title and
-- assign it to the output parameter.
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title

RETURN
GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-03 : 08:21:03

Davey's example works for every data type EXCEPT text though. Since you can't manipulate a text variable at all, the best solution would be Corey's: return the text data from a table.

Daveyboy69
Starting Member

11 Posts

Posted - 2004-09-03 : 11:17:22

Oops, lesson 1: Read the message properly before responding.

Apologies.

;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources