0

Can I force the procedure to use array of parameters?

Something similar to Console.WriteLine(String msg, params[]) in C#

I need it to pass column names of table in procedure, something similar to INSERT INTO operation, but need to pass n arguments

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bartłomiej Sobieszek
  • 2,692
  • 2
  • 25
  • 40
  • possible duplicate of [Can I send array of parameter to store procedure?](http://stackoverflow.com/questions/5961166/can-i-send-array-of-parameter-to-store-procedure) – Kyle Hale May 20 '14 at 20:16
  • Yeah but this is working like string parsing, what about CLR-UDF functions? – Bartłomiej Sobieszek May 20 '14 at 20:18
  • 1
    You can use a table-valued parameter. But more generally, there are several questions on Stack Overflow that already address this pattern. – Kyle Hale May 20 '14 at 20:21
  • 1
    ``[Here](http://stackoverflow.com/a/19957857/1362136) is an example of using a table-valued parameter `` – Stephen Byrne May 20 '14 at 20:34

1 Answers1

1

As said above, a Table Valued Parameter is probably your best option here.

I've also had some success with XML parameters (might be easier to generate from C#, depending on the host), and allows some more complexity on the parameter side.

Before SQL 2008, I used pipe (|) separated strings and a string splitter function, which also work fine.

M.Stoop
  • 119
  • 2
  • 1
    [Erland Sommarskog wrote an excellent reference on TVPs](http://www.sommarskog.se/arrays-in-sql-2008.html) which covers the use case of arrays and lists in SQL Server. Really good resource for anyone who's not used them before. – Steve Pettifer May 21 '14 at 10:01