Just curious, why have you written this as a Stored Procedure instead of a simple piece of SQL in your application? I don't know if it will help if you change it though, but a Stored Procedure feels like an unecessary over-complication here.
Thats just an part of the stored procedure and it has many more SQL commands after this statement
Let me know if u have any idea
I can give some more hints on how its behaving
if i execute from query analyzer using
<SP_NAME> "'value1','Value2','Value3'" . It works fine but as soon as that paramter values seperated by comma exceeds 128 characters , that gives a problem
Ah. Well, I should have thought of this earlier, but I don't think T-SQL allows you to pass multiple values for a single parameter. I think the normal way round it is to call a procedure repeatedly for each value, or to pass all the values as CSV and do some string processing in your procedure.
Not quite, this would be handling the parameter as CSV:
Notice the difference in the type of quotes I use. SQL server understands the contents of double quotes as something different then single quotes. All literal string values used in T-SQL/SQL (as far as SQL Server is concerned) should be defined in single quotes.
The string manipulation stuff I mention are T-SQL functions like SUBSTRING. This is one way of breaking your single-value parameter up into the multiple values you need. However its far better to call the procedure repeatedly rather than trying to interpret a single parameter value like this. You would just be introducing another point where the code could go wrong. [ June 15, 2005: Message edited by: Paul Sturrock ]
Post by:autobot
Space seems cool in the movies, but once you get out there, it is super boring. Now for a fascinating tiny ad:
a bit of art, as a gift, the permaculture playing cards