TEXT datatype in a Stored Procedure?

[cfaulkner]

I need a way of passing strings larger than varchar or nvarchar (8,000) into a stored procedure. There seems to be a problem with using the text datatype; like you can't. Can anyone offer a creative solution for this?

Thanks so much!

 

[gmmastros]

You can. I've done it before.

Perhaps you could give us more detail regarding what doesn't work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cfaulkner]

This a vendor app that we have to work with. Basically the string that is being passed in is a query that then must be run (EXEC), so that may be another limitation. So, say we are passing in a 10k string (query) that is then run from the SP. Obviously we can't pass in the 10k, but we could pass it in in pieces. In any case I'm still very interested in your solution. What did you do?

Thanks!

 

[gmmastros]

Well, passing a text parameter is no different than any other data type.

Ex:

[tt][blue]
Create Procedure Test @Value Text
As
......
[/blue][/tt]

I suspect the problem you are having is with the EXEC part.

@string_variable

Is the name of a local variable. @string_variable can be of [!]char, varchar, nchar, or nvarchar[/!] data type with a maximum value of the server's available memory. If the string is greater than 4,000 characters, concatenate multiple local variables to use for the EXECUTE string. For more information about system-supplied SQL Server data types, see Data Types.

Lookup (in books on line) the sp_executesql command.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cfaulkner]

Thanks