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
varsha2Asked:
Who is Participating?
 
tschill120198Connect With a Mentor Commented:
Surely there must be a limit at some point... say, 10 varchar parameters?  20? 30?  They don't all have to be used, so if a particular query doesn't need all of them, they can default to null; the sproc doesn't parse them if they are null.  This is probably your best solution.
An alternative solution would be to have a seperate sproc that is called repeatedly (once for each value you want to query against) and this sproc inserts the values into a table.  When all values have been entered, you call the sproc that performs the main query.


0
 
tschill120198Commented:
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.
0
 
varsha2Author Commented:
I cannot pass the parameter using a varchar field because all the parameters together exceeds 255 characters
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
tschill120198Commented:
How about using multiple varchar parameters?
0
 
varsha2Author Commented:
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
0
 
ivanhCommented:
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.
0
 
tschill120198Commented:
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?
0
 
ivanhCommented:
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'".
0
 
tschill120198Commented:
ivanh is correct... his answer should work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.