Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-05-06 : 08:02:50
|
| Daryl writes "Is there any way to pass a table variable to a stored procedure?I am using a stored proc to recalc some fields, but the procedure needs to know which records to recalc. I was hoping to store the primary keys in a table variable and pass them to this procedure. I did not want to use a CSV string.The values would be retrieved from different triggers, saved in the table variable and then passed to the stored procedure." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-06 : 08:05:09
|
quote:
I did not want to use a CSV string.
Why not? They work great:http://www.sqlteam.com/item.asp?ItemID=2652http://www.sqlteam.com/item.asp?ItemID=637If you really can't use a CSV, you can create a temp table in the first procedure, insert the rows you need into it, and then call the 2nd procedure. It will have the scope to see the temp table and can process it normally. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-06 : 08:23:52
|
| If you're worried about concurrency, I do it this way:have two tables:DataHeaders (ID identity)DataDetails (ID [FK to dataheaders.ID], field1, field2, ...)Then, when you need to pass data to a stored proc:1) add a row to dataheaders, get back the identity ID of that row using @@IDENTITY2) using that ID to tag your details, insert data into the details table3) call your stored proc, passing in that ID.4) the stored proc then just uses the data in the table, filtered by the proper ID, and also deletes the rows from both tables when done.That's just an idea. it works pretty well, has no concurrency issues, and lets you pass very complex table or data stuctures to a stored procedure without needing to use CSV's -- which don't work too well for large tables or when there are a large number of columns to pass.Between the CSV options Rob gave and this idea, hopefully something will work for you.- Jeff |
|
|
|
|
|
|
|
|
Passing Table Variables