Use text output parameters in Stored Procedures)

This post is reposted from the blog of .

Today, when I was writing a program, I used the stored procedure of the output text type, but it has been unavailable for a long time. Check the information on msdn. The following information is available on msdn:

Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/tsqlref/ts_create_4hk5.asp

Output

Indicates that the parameter is a return parameter. The value of this option can be returned to Exec [ute]. Use output parameters to return information to the calling procedure.Text, ntext, and image parameters can be used as output parameters. An output parameter using the output keyword can be a cursor placeholder.

Later, I checked the information on Google and consulted many people, and found that the description of msdn should be worth discussing. The reason is as follows: the text type can be used as the output parameter, but it cannot be assigned a value. Once a value is assigned, an error is returned, which means no output is used at all.

For example, the following is a stored procedure without a value assignment operation.

Create proc p_test @ o text = NULL

Out

Go

Exec p_test

Go

Drop proc p_test

However, once a value assignment operation exists, the problem arises. For example, the following stored procedure:

Create proc p_test @ o text = NULL

Out

Select @ o = 'X'

Go

The following error is reported during creation:

Server: MSG 409, level 16, state 1, procedure p_test, line 3

The assignment operator operation cannot take a text data type as an argument.

 

Some discussions on Google:

Http://groups.google.com/groups? Hl = en & LR = & Ie = UTF-8 & OE = UTF-8 & threadm = E % 23bc093dbha. 1828% 40tkmsftngp05 & rnum = 2 & Prev =/groups % 3fq % 3 dtext, % 2 bntext, % 2 band % 2 bimage % 2 bparameters % 2 bcan % 2bbe % 2 bused % 2bas % 2 boutput % 2 bparameters % 26hl % 3den % 26lr % 3d % 26ie % 3dutf-8% 26oe % 3dutf-8% 26 selm % 3de % 2523bc093dbha. 1828% 2540tkmsftngp05% 26 rnum % 3d2

Http://groups.google.com/groups? Hl = en & LR = & Ie = UTF-8 & OE = UTF-8 & Q = text % 2C + ntext % 2C + and + image + parameters + can + be + used + As + output + Parameters & btng = Google + search

There are many methods to read text-type variables:

Sqlserver does not allow you to assign the blobs big data type to a local variable. You can only get the corresponding pointer and then read the relevant information based on the pointer.

Generally, sqldatareader is used. For more information, see

Http://www.microsoft.com/china/msdn/adonet.asp

If you do not want to use sqldatareader or use sqlhelper. executenonquery, you can retrieve the data pointer first and then read the data based on the pointer. For more information, see

[Conserving resources when writing blob values to SQL Server]

Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/cpguide/html/cpconservingresourceswhenwritingblobvaluestosqlserver. asp

In addition, there is no problem when image is used as the output parameter, such as the following stored procedure:

Create procedure outputcoverbyisbn

(@ Isbnwanted varchar (20 ),

@ Coverout image output)

As

Select @ coverout = cover from covers where ISBN = @ isbnwanted

Select ISBN, version, filename, @ coverout cover from covers where ISBN =

@ Isbnwanted

Go