1

I need to insert values from a table into a sproc. For example:

exec mysproc @param1='col1', @param2='col2'

This can be done using a cursor but is there some way to do it via a set operation?

4thSpace
  • 43,672
  • 97
  • 296
  • 475
  • http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/passing-an-array-or-table-parameter-to-a-stored-procedure.aspx – Robert Harvey Jan 03 '13 at 17:13
  • http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/ – Robert Harvey Jan 03 '13 at 17:14
  • http://stackoverflow.com/questions/5595353 – Robert Harvey Jan 03 '13 at 17:14
  • @RobertHarvey - Where does the OP mention .NET? – Martin Smith Jan 03 '13 at 17:23
  • @MartinSmith: It's SQL Server. You really think he's trying to pass parameters from Java or Python? – Robert Harvey Jan 03 '13 at 17:24
  • 1
    @RobertHarvey - Could be doing it in TSQL. – Martin Smith Jan 03 '13 at 17:24
  • @MartinSmith: OK. Well, that's why it says *possible* duplicate. We'll wait and see what the OP says. – Robert Harvey Jan 03 '13 at 17:26
  • It isn't in .NET. It's pure sql (MS SQL Server) and the examples you posted are using .NET and also modifying the sprocs. I won't be modifying any sprocs. Whoever closed this didn't take the time to understand the question. I don't even have .NET in the tags. – 4thSpace Jan 03 '13 at 17:43
  • @4thSpace - You'll need to address the comment `@Robert Harvey` or flag it to be reopened. Though the inability to `ALTER` the stored procedure is a show stopper. It will just have to be called as many times as you have rows. There is no way around it. – Martin Smith Jan 03 '13 at 17:51
  • Have you tried doing an Internet search for this? http://www.sommarskog.se/share_data.html#tableparam – Robert Harvey Jan 03 '13 at 17:53
  • @RobertHarvey - That would still need the proc to be altered to reference the temp table. – Martin Smith Jan 03 '13 at 17:53
  • OK. So what's the problem with using a cursor? – Robert Harvey Jan 03 '13 at 17:54
  • Ok, it looks like all options have been exhausted and a cursor is the way to go for this scenario? – 4thSpace Jan 03 '13 at 18:01
  • I don't know. Why don't you want to use a cursor? Seems strange that you're looking for some other solution when you already have a working one. Are you looking for something more elegant? Something that runs faster or uses less memory? – Robert Harvey Jan 03 '13 at 18:04
  • @Robert: Why do you have so many presumptions? I never said I didn't want to use a cursor. I was exploring if there was a set operation to handle my scenario. If you believe a cursor is the way to go, post it as the answer and I'll accept. – 4thSpace Jan 03 '13 at 18:07

2 Answers2

1

I imagine that the method you choose would be based on the amount of time you have available and it's difficult to say which of these methods is most time consuming without being more intimate with the logic.

There are a few approaches to this problem.

  1. As Robert Harvey has alluded to, you should maybe look at maybe modifying the proc to accept a table valued parameter (if you are using SQL Server 2008 upwards). If not, you could create a scalar XML parameter that is "decoded" in to a table inside the proc.
  2. Populate a #table with your "parameter data" and a ROW_NUMBER() and use a WHILE loop to call the proc for each row in your #table.
  3. Create a CURSOR (I hate giving CURSOR advice) of type FAST_FORWARD and iteratively call the procedure.
  4. Dynamic SQL; build up a SQL command string using EXEC or preferably SP_EXECUTESQL.
  5. My opinion is that first prize would be to re-engineer the proc to accept parameter filters. Going on the assumption that the dataset you wish to create parameters from is the result of a filtered query:
SELECT Moo, Meow
FROM Woof
WHERE Fu = @ParmX 
AND Bar = @ParmY

Your proc should be called with @ParmX, @ParmY and the logic inside would then proceed in a set based manner.

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
MarkD
  • 5,276
  • 1
  • 14
  • 22
  • 1
    BTW: That is a known issue with code blocks immediately after lists. [I used the HTML comment workaround here](http://meta.stackexchange.com/a/99637) – Martin Smith Jan 03 '13 at 18:59
1

It is not possible to invoke an sproc as part of a "set operation". Probably, the reason for that is that the sproc might have arbitrary side-effects like modifying data, sending additional result sets (!) or shutting down the server.

A cursor is the canonical approach to this. (Alas.)

You could modify the sproc to take a TVP, of course. Not sure if that is workable for you.

usr
  • 168,620
  • 35
  • 240
  • 369