Sunday, September 11, 2011

How to call a Stored Procedure for each record in a table

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

1 comment:

Anonymous said...

I think your link to the forum on dotnetspider is incorrect.

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.