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

• JeffB

  Hall of Fame

  Points: 3541

  More actions

  June 15, 2006 at 1:02 pm

  #644061

  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

  June 15, 2006 at 1:10 pm

  #644065

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

  June 15, 2006 at 1:18 pm

  #644066

  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

  June 15, 2006 at 1:52 pm

  #644074

  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