varsha2
asked on
Stored Procedure
Hi
I am trying to pass parameters to a stored procedure , this paremeter will be used in an "in" clause. Since i cannot pass an array as parameter i am trying to concatenate the different values in the or class into a string and send as a varchar and execute the SQL in the procedure using EXEC statement. But sometimes this concatenated "in" clause parameters exceeds 255 characters. so i cannot pass it as a varchar. what would be the solution
I am trying to pass parameters to a stored procedure , this paremeter will be used in an "in" clause. Since i cannot pass an array as parameter i am trying to concatenate the different values in the or class into a string and send as a varchar and execute the SQL in the procedure using EXEC statement. But sometimes this concatenated "in" clause parameters exceeds 255 characters. so i cannot pass it as a varchar. what would be the solution
You could pass the values in using a varchar field, parse each value into a table, and join to that table in your query instead of using "in"... performance won't be optimal due to the parsing, though.
ASKER
I cannot pass the parameter using a varchar field because all the parameters together exceeds 255 characters
How about using multiple varchar parameters?
ASKER
I craete the in clause dynamically and i may not be able to determine how many varchar i might need to send as input parameter. this basically prevents me from determining how many input parameters the stored procedure can have
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The quick answer would be as a text datatype, but I'm not sure
what that does for performance. I use it on one of my queries
for the same type of reason and I don't notice any performance problems.
what that does for performance. I use it on one of my queries
for the same type of reason and I don't notice any performance problems.
Agreed... a text parameter would be a cleaner solution to the parameter issue. But how would you parse the text field for the different values you are searching on?
He said that he was going to concatenate the string into the
format for execution in the IN statement and then run the EXEC statement to run the query.
EXEC ("select * from mytable where name in (" + in_str + ")")
in_str would be something like "'a','b','c','d'".
format for execution in the IN statement and then run the EXEC statement to run the query.
EXEC ("select * from mytable where name in (" + in_str + ")")
in_str would be something like "'a','b','c','d'".
ivanh is correct... his answer should work.