June 15, 2006 at 12:58 pm
Can I use a table variable to pass several similar values into the Stored Procedure? What do I pass as a parameter then if calling an SP from ADO?
Regards,Yelena Varsha
June 15, 2006 at 1:02 pm
You can't. Check out this post, about 3 below yours: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=287381
June 15, 2006 at 1:10 pm
>>to pass several similar values
Do you mean an "array" that may have a variable number of "rows" ?
You can pass it as XML and use OpenXML inside the SP to join the XML to other SQL tables.
June 15, 2006 at 1:18 pm
Yes, that exactly what I mean. One of the developers would like to pass several similar values as many as the user selects and then say: where something in (value1,value2,value3...) He was thinking along the lines of passing as one string but I told him he would have to parse it.
I was thinking that maybe he should to truncate and populate a temp table or actual table with user-selected values and then say: where something in (select field1 from mytable) but decided to check table vars
Regards,Yelena Varsha
June 15, 2006 at 1:52 pm
Hi,
Thanks all for the ideas and links, I checked these and forwarded to the developer. I also tested and told him that if he uses a dynamic string with the whole query as a string in SP and a list of values as a parameter in the format 'value1,value2,....' and then in SP concatenates these 2 and use sp_executesql it works. The only thing that as a DBA I don't like concatenating several values into one.
Regards,Yelena Varsha
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy