0

I would like to insert a range of values into a pair of SQL tables, with data requiring some pre-processing (like attaching a common time stamp and some revamp of the data in both tables). Ideally I prefer to shield my code from direct SQL statements, opting to use stored procedures instead. How could I pass these values to a stored procedure?

Thank you.

EDIT I am using SQL Server 2012 but want to be compatible with 2008. If there is a 2012-specific solution, please let me know, I will gladly consider it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
gt6989b
  • 4,125
  • 8
  • 46
  • 64

3 Answers3

4

Send the data over as XML and convert it into multiple rows. Something like

SELECT
Tbl.Col.value('(span/b)[1]', 'nvarchar(max)')   AS [key],
Tbl.Col.value('(span/var)[1]', 'nvarchar(max)') AS [position],
Tbl.Col.value('@title', 'nvarchar(max)')        AS [tooltip],
Tbl.Col.value('(code)[1]', 'nvarchar(max)')     AS [value]

FROM @xmlData.nodes('/ul/li') Tbl(Col)

See for more details

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
3

If you are developing for SQL Server 2008+ then you can use table-valued parameters. See documentation.

Alexander Simonov
  • 1,564
  • 1
  • 9
  • 15
  • That means I have to create and populate a temp-table inside the code and then pass it to the stored procedure? Would it be better to stuff it in the VALUES string to be executed by the proc in the dynamic sql? – gt6989b Nov 01 '13 at 18:47
  • @gt6989b, It's up to you, but I would strongly recommend to use dynamic sql only as a last resort. Read [this](http://http://www.sommarskog.se/dynamic_sql.html). – Alexander Simonov Nov 01 '13 at 19:45
  • Aren't I forced to do the same thing either way? I am working in Python, not C#. Now I must execute dynamic SQL to build the temporary table and insert into it, and then pass the result to a stored procedure. Stringing avoids all of this issue. I really dislike raw SQL code in my Python – gt6989b Nov 01 '13 at 20:34
3

You can use table-valued parameters in the stored procedure. This allows to pass a table as a parameter so you can define the structure of the parameter as you want.

See 'Use Table-Valued Parameters (Database Engine)' on MSDN - http://technet.microsoft.com/en-us/library/bb510489.aspx

To call such a SP, see, e.g., How to pass table value parameters to stored procedure from .net code. This question was answered a couple of times.

Community
  • 1
  • 1
E.K.
  • 419
  • 2
  • 3
  • That means I have to create and populate a temp-table inside the code and then pass it to the stored procedure? Would it be better to stuff it in the VALUES string to be executed by the proc in the dynamic sql? – gt6989b Nov 01 '13 at 18:48
  • Updated answer to answer your question – E.K. Nov 01 '13 at 18:51
  • no, that would not be better, table value parameters are a much better option – BlackTigerX Nov 01 '13 at 19:06
  • @BlackTigerX But I am basically forced to do the same thing. I am working in Python, not C#. Now I must execute dynamic SQL to build the temporary table and insert into it, and then pass the result to a stored procedure. Stringing avoids all of this issue. I really *dislike* raw SQL code in my Python. – gt6989b Nov 01 '13 at 20:33