How to supply values to sproc from table?

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?

  • How to SQL Server traffic is encrypted?
  • Full Text Catalog is destroyed after changes in table
  • How to Insert Multiple Row from one Column
  • spark dataframe not appending to the table
  • SQL Select Permissions
  • 2 Solutions collect form web for “How to supply values to sproc from table?”

    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.

    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
    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
    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.