SQL Call Stored Procedure for each Row without using a cursor

Marc's answer is good (I'd comment on it if I could work out how to!)
Just thought I'd point out that it may be better to change the loop so the `SELECT` only exists once (in a real case where I needed to do this, the `SELECT` was quite complex, and writing it twice was a risky maintenance issue).

-- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
SET @CustomerIDToHandle = 1

-- as long as we have customers......
WHILE @LastCustomerID <> @CustomerIDToHandle
BEGIN
SET @LastCustomerId = @CustomerIDToHandle
-- select the next customer to handle
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerId
ORDER BY CustomerID

IF @CustomerIDToHandle <> @LastCustomerID
BEGIN
-- call your sproc
END

END

I'd use the accepted answer, but another possibility is to use a table variable to hold a numbered set of values (in this case just the ID field of a table) and loop through those by Row Number with a JOIN to the table to retrieve whatever you need for the action within the loop.

DECLARE @RowCnt int; SET @RowCnt = 0 -- Loop Counter

-- Use a table variable to hold numbered rows containg MyTable's ID values
DECLARE @tblLoop TABLE (RowNum int IDENTITY (1, 1) Primary key NOT NULL,
ID INT )
INSERT INTO @tblLoop (ID) SELECT ID FROM MyTable

-- Vars to use within the loop
DECLARE @Code NVarChar(10); DECLARE @Name NVarChar(100);

WHILE @RowCnt < (SELECT COUNT(RowNum) FROM @tblLoop)
BEGIN
SET @RowCnt = @RowCnt + 1
-- Do what you want here with the data stored in tblLoop for the given RowNum
SELECT @Code=Code, @Name=LongName
FROM MyTable INNER JOIN @tblLoop tL on MyTable.ID=tL.ID
WHERE tl.RowNum=@RowCnt
PRINT Convert(NVarChar(10),@RowCnt) +' '+ @Code +' '+ @Name
END

This is a variation on the answers already provided, but should be better performing because it doesn't require ORDER BY, COUNT or MIN/MAX. The only disadvantage with this approach is that you have to create a temp table to hold all the Ids (the assumption is that you have gaps in your list of CustomerIDs).

That said, I agree with @Mark Powell though that, generally speaking, a set based approach should still be better.

DECLARE @tmp table (Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, CustomerID INT NOT NULL)
DECLARE @CustomerId INT
DECLARE @Id INT = 0

INSERT INTO @tmp SELECT CustomerId FROM Sales.Customer

WHILE (1=1)
BEGIN
SELECT @CustomerId = CustomerId, @Id = Id
FROM @tmp
WHERE Id = @Id + 1

IF @@rowcount = 0 BREAK;

-- call your sproc
EXEC dbo.YOURSPROC @CustomerId;
END

In case the order is important

--declare counter
DECLARE @CurrentRowNum BIGINT = 0;
--Iterate over all rows in [DataTable]
WHILE (1 = 1)
BEGIN
--Get next row by number of row
SELECT TOP 1 @CurrentRowNum = extendedData.RowNum
--here also you can store another values
--for following usage
--@MyVariable = extendedData.Value
FROM (
SELECT
data.*
,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNum
FROM [DataTable] data
) extendedData
WHERE extendedData.RowNum > @CurrentRowNum
ORDER BY extendedData.RowNum

--Exit loop if no more rows
IF @@ROWCOUNT = 0 BREAK;

--call your sproc
--EXEC dbo.YOURSPROC @MyVariable
END

A better solution for this is to

> 1. Copy/past code of Stored Procedure
> 2. Join that code with the table for which you want to run it again (for each row)

This was you get a clean table-formatted output. While if you run SP for every row, you get a separate query result for each iteration which is ugly.

I had some production code that could only handle 20 employees at a time, below is the framework for the code. I just copied the production code and removed stuff below.

ALTER procedure GetEmployees
@ClientId varchar(50)
as
begin
declare @EEList table (employeeId varchar(50));
declare @EE20 table (employeeId varchar(50));

insert into @EEList select employeeId from Employee where (ClientId = @ClientId);

-- Do 20 at a time
while (select count(*) from @EEList) > 0
BEGIN
insert into @EE20 select top 20 employeeId from @EEList;

-- Call sp here

delete @EEList where employeeId in (select employeeId from @EE20)
delete @EE20;
END;

RETURN
end

DECLARE @SQL varchar(max)=''

-- MyTable has fields fld1 & fld2

Select @SQL = @SQL + 'exec myproc ' + convert(varchar(10),fld1) + ','
+ convert(varchar(10),fld2) + ';'
From MyTable

EXEC (@SQL)

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

I had a situation where I needed to perform a series of operations on a result set (table). The operations are all set operations, so its not an issue, but...
I needed to do this in multiple places. So putting the relevant pieces in a table type, then populating a table variable w/ each result set allows me to call the sp and repeat the operations each time i need to .


While this does not address the exact question he asks, it does address how to perform an operation on all rows of a table without using a cursor.

@Johannes offers no insight into his motivation , so this may or may not help him.

my research led me to this well written article which served as a basis for my solution

[To see links please register here]

Here is the setup

drop type if exists cpRootMapType
go

create type cpRootMapType as Table(
RootId1 int
, RootId2 int
)

go
drop procedure if exists spMapRoot2toRoot1
go
create procedure spMapRoot2toRoot1
(
@map cpRootMapType Readonly
)
as

update linkTable set root = root1
from linktable lt
join @map m on lt.root = root2

update comments set root = root1
from comments c
join @map m on c.root = root2

-- ever growing list of places this map would need to be applied....
-- now consolidated into one place


here is the implementation


... populate #matches

declare @map cpRootMapType
insert @map select rootid1, rootid2 from #matches
exec spMapRoot2toRoot1 @map