Table Variable as SP Parameter?

Question

Table Variable as SP Parameter?

Yelena Varshal

SSC-Dedicated

Points: 34286
More actions
June 15, 2006 at 12:58 pm

#113538

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

JeffB Hall of Fame Points: 3541 More actions · Answer 1

You can't. Check out this post, about 3 below yours: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=287381

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

>>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.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

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