Without using Cursors how to call a stored procedure for each record in a table? This was a question asked in a forum yesterday. I come up with an alternate WHILE loop logic which is shown below:
--Sample table to hold test input data
Create table #temp_table(id int)
GO
--Lets insert some dummy data
Insert into #temp_table values (1)
Insert into #temp_table values (2)
Insert into #temp_table values (3)
Insert into #temp_table values (4)
GO
--Procedure which would just take the input
--and print current date + that number of days
CREATE PROCEDURE dbo.printdate
(
@num INT
)
AS
BEGIN
SET NOCOUNT ON
SELECT GETDATE() + @num as [New Date]
END
GO
--Solution
DECLARE @id INT
DECLARE @cnt INT
SET @id = 0
SELECT @cnt = MAX(id) FROM #temp_table
WHILE @id < @cnt
BEGIN
SELECT TOP 1 @id = MIN(id)
FROM #temp_table
WHERE id > @id GROUP BY id ORDER BY id
EXEC dbo.printdate @num = @id
END
GO
Comments
Also, your method won't work right if the id values have any duplicates, or if a compound set of columns is needed to make them unique. In SQL 2005 or greater, probably better to use the ROW_NUMBER() function to give you a single column of unique "row counting" numbers to loop on, in the right order, with one query to the original table--and consider putting that in a table variable, rather than a temp table, if the subset of data to be looped through is not large.