I have a stored procedure that acce

[apollo11]

I have a stored procedure that accepts a list of (comma delimited) IDs as a parameter. The data type is nvarchar. I then use this parameter to fire a query that has a IN statement, something like "SupplierID in (' + @SupplierID + ')" where @SupplierID is teh param in question.
Initially I had set the param to nvarchar(2000) but then the length of the data started getting bigger and I had to increase it to nvarchar(4000) which is the max. limit for nvarchar.
There are chances that the data might get bigger. Is there any other way to define this param that can accept a value higher than 4000 chars. I tried ntext but on compilation I get an error: "Invalid operator for data type. Operator equal add type equals ntext"
It is probably not accepting a string concatenation operation on type ntext.

Any help on this would be appreciated. Thanks !

 

[cmmrfrds]

This would require a redesign of the SP, but if your front end app is using ADO then a temporary table could be used instead of the string of params. As long as the connection remains open then multiple sql statements can be executed. For instance from the ADO app, open a connection and insert the params in a temporary table. The SP would need to join the temparory table instead on using the IN list. After creating the temporary table then execute the SP on the same connection. Should drop the temp table either at the end of the SP or as a separate sql statement - I believe the temp table will go out of scope when the connection is deleted but better to make sure by dropping the table. Lastly, set the connection to nothing.

 

[apollo11]

Thanks a lot..
I have several options in case I need to redesign the app but I was looking for a quick resolution without changing the business object.

 

[cmmrfrds]

I take it your character base is unicode, otherwise, the varchar instead of the nvarchar will hold 8000 characters.

 

[tlbroadbent]

You could pass in two or more parameters and concatentate them when executing the statement. If you use sp_executesql to execute the string, the string size is limited only by available memory.

If you use SQL 2000, you could implement a User-Defined Function (UDF) that parses a deleimited string and returns a table. I gave some info about his option in thread183-292139. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[krotha]

Hi apollo11
Did you solve this, Right now I have a similiar situation like this. In my case, parameter length is 24000 characters, it may increase in future. As tlbroadbent suggested I tried Fn_Split(), this works fine if it is less than 8000 characters, I can't declare as a text in that function.

Any Help is appreciated.