Pass multiple values to single parameter in SQL Server 2012

Question

Need your help again.

Wanted to know is there any way to pass multiple value to single parameter in SQL Server 2012?

For example,

id     item_name  item_code
    1      boots       23
    2       hat        5
    3      dress       11

Let's say in the case above, I have parameter @inp_code

What to do, so when I will pass '23' and '11' to the parameter so I could get the output below:

id      item_name   item_code
   1       boots        23
   3       dress        11

how about doing some (any) research? Just a few minutes worth? What did you find when you typed "sql server 2012 table valued parameters" in a search engine??? — Mitch Wheat
– Mitch Wheat, Commented Apr 1, 2013 at 9:08

Dave Simione · Accepted Answer · 2013-04-01 09:11:19Z

Here is a link to the MSDN documentation

The overall concept is this.

You'll create a new table type (listed in SSMS under /Programmability/Types/User-Defined Table Types. This will look like a normal SQL table, except the header is different (CREATE TYPE AS TABLE instead of CREATE TABLE)
Once the table type is created, you can define a parameter on your stored proc of the table type.
In a SQL Script, you define a variable of the table type, INSERT into it, and pass it to the stored proc.
Inside you stored proc, you can now access the parameter like a table (meaning it is a rowset, not a single value, so think set-based, not single value)

One item of note - these are not supported via ADO.NET directly. You would need to create a script that would insert multiple values into the table variable, passing that to the stored proc, and execute the entire script from within your programming language.

Collectives™ on Stack Overflow

Pass multiple values to single parameter in SQL Server 2012

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related