SQL Call Stored Procedure for each Row without using a cursor

Generally speaking I always look for a set based approach (sometimes at the expense of changing the schema).

However, this snippet does have its place..

-- Declare & init (2008 syntax)DECLARE @CustomerID INT = 0-- Iterate over all customersWHILE (1 = 1) BEGIN    -- Get next customerId  SELECT TOP 1 @CustomerID = CustomerID  FROM Sales.Customer  WHERE CustomerID > @CustomerId   ORDER BY CustomerID  -- Exit loop if no more customers  IF @@ROWCOUNT = 0 BREAK;  -- call your sproc  EXEC dbo.YOURSPROC @CustomerIdEND

You could do something like this: order your table by e.g. CustomerID (using the AdventureWorks Sales.Customer sample table), and iterate over those customers using a WHILE loop:

-- define the last customer ID handledDECLARE @LastCustomerID INTSET @LastCustomerID = 0-- define the customer ID to be handled nowDECLARE @CustomerIDToHandle INT-- select the next customer to handle    SELECT TOP 1 @CustomerIDToHandle = CustomerIDFROM Sales.CustomerWHERE CustomerID > @LastCustomerIDORDER BY CustomerID-- as long as we have customers......    WHILE @CustomerIDToHandle IS NOT NULLBEGIN    -- call your sproc    -- set the last customer handled to the one we just handled    SET @LastCustomerID = @CustomerIDToHandle    SET @CustomerIDToHandle = NULL    -- select the next customer to handle        SELECT TOP 1 @CustomerIDToHandle = CustomerID    FROM Sales.Customer    WHERE CustomerID > @LastCustomerID    ORDER BY CustomerIDEND

That should work with any table as long as you can define some kind of an ORDER BY on some column.

DECLARE @SQL varchar(max)=''-- MyTable has fields fld1 & fld2Select @SQL = @SQL + 'exec myproc ' + convert(varchar(10),fld1) + ','                    + convert(varchar(10),fld2) + ';'From MyTableEXEC (@SQL)

Ok, so I would never put such code into production, but it does satisfy your requirements.