Exec stored procedure with select as parameter

Question

I have a stored procedure on sql server that takes 2 parameters. I want to run the stp for every row returned by a select.

CREATE PROCEDURE stpMySTP @param1, @param2
AS BEGIN
   --select something from database regarding this parameters
END

I would like to run this stp and pass a list of parameters for @param2 returned by a select statement.

Something like :

   EXEC stpMySTP '',(SELECT ....)

Any idea ?

possible duplicate of [How do I execute a stored procedure once for each row returned by query?](http://stackoverflow.com/questions/886293/how-do-i-execute-a-stored-procedure-once-for-each-row-returned-by-query) — Pondlife, Mar 06 '13 at 15:09

granadaCoder · Accepted Answer · 2020-10-12T11:44:05.037

If I'm reading this question correctly (I may not be) :::

This is one of those things where a cursor is a possible solution. BUT, cursors are usually terrible solutions.

USE [Northwind]
GO


/*  uspCustomerGetSingleWithOrders 'SIMOB'  */

CREATE  PROCEDURE [dbo].[uspCustomerGetSingleWithOrders]  (
    @customerId varchar(12)
)

AS 

SET NOCOUNT ON 


Select 
    --CustomerID ,
    --ContactName , 
    --City
    CustomerID,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax
from 
    dbo.Customers

WHERE
    LTRIM(RTRIM(UPPER(CustomerID))) = LTRIM(RTRIM(UPPER(@customerId)))


Select 
    OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Freight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry
from 
    dbo.Orders o
WHERE
    LTRIM(RTRIM(UPPER(o.CustomerID))) = LTRIM(RTRIM(UPPER(@customerId)))

SET NOCOUNT OFF


GO

and now a cursor ( :< )

Use Northwind

GO


declare @PROC_fetch_status int


declare @CustomerID varchar(12)
declare @ContactName varchar(24)


--    Select * from Customers
DECLARE curCustomers CURSOR FAST_FORWARD FOR select CustomerID , ContactName
from Customers Order by ContactName
OPEN curCustomers


-- Perform the first fetch.
fetch curCustomers into @CustomerID , @ContactName


select @PROC_fetch_status = @@fetch_status

    IF @PROC_fetch_status <> 0
 begin
  print 'No Records'
 end

    WHILE @PROC_fetch_status = 0
    BEGIN

        print 'Current Customer  = ' + @CustomerID + '  :  ' + @ContactName
        print '------------------------------------'
        print ''
        EXEC dbo.uspCustomerGetSingleWithOrders @CustomerID
        print ''
        print ''

        FETCH NEXT FROM curCustomers INTO @CustomerID , @ContactName
        select @PROC_fetch_status = @@fetch_status

    END
CLOSE curCustomers
DEALLOCATE curCustomers

But I would suggest reading up on "set based" versus "row by agonizing row" philosophies. If you're interested, please leave a note.

your solution will work. very slow (i have 53601 records) but i think it will work. in the mean time i created the executions as string and i worked with 2 temporary tables. — sebastian.roibu, Mar 07 '13 at 07:09
Yep. Thus my original comment "cursors are usually terrible solutions". — granadaCoder, Mar 07 '13 at 13:28

score 1 · Answer 2 · answered Mar 06 '13 at 15:06

I would suggest the following:

if your argument list is fair simple, then use a string as @param2. Like '|1|2|100500|'. Then split the string withing the procedure and use these parameters
If your prosedure returns a value, then go for stored function and use the function call within a SELECT statement
Use a dedicated table where you can push interested values. Like (SessionID, paramId, paramValue). IN thios case your @param2 is a SessionId from this extra table

Exec stored procedure with select as parameter

2 Answers2