How to return text data type from stored procedure.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Archana - 28 Feb 2007 14:13 GMT

Hi all,

I am having one stored procedure which is returing parameter having
text data type.

This paramter has to take value from table which have column with
datatype as text.

How will i set value to parameter having text datatype to value
present in table?

Any help will be truely appreciated.

Reply to this Message

SQL_Beginner - 28 Feb 2007 14:26 GMT

> I am having one stored procedure which is returing parameter having
> text data type.
[quoted text clipped - 4 lines]
> How will i set value to parameter having text datatype to value
> present in table?

You would just SELECT it, not set it to a variable. You can't have a
variable of type TEXT. And you can't RETURN anything other than an INT, so
I assume you meant OUTPUT, not RETURN.

In SQL Server 2005 you can use VARCHAR(MAX) which is a first-class
citizen -- meaning you can DECLARE @foo VARCHAR(MAX) and proceed with
storing 2GB of text in there if you want.

A

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 28 Feb 2007 15:12 GMT

> I am having one stored procedure which is returing parameter having
> text data type.

You can't return anything but an INT, and it is *NOT* meant to return
*DATA* -- return values are meant to return error/status. Single data
elements that are not part of a resultset should be "returned" via an OUTPUT
parameter.

And you can't store TEXT as a local variable... maybe you could use
VARCHAR(MAX) in SQL Server 2005. Otherwise all you can do is SELECT
TextColumn FROM table and have the application consume the result that way.

Signature

Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Reply to this Message