• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 184
  • Last Modified:

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
0
varsha2
Asked:
varsha2
  • 5
  • 2
  • 2
1 Solution
 
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
 
tschill120198Commented:
How about using multiple varchar parameters?
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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
 
tschill120198Commented:
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now