Run Stored Procedure with table data as parameter
I have number of ids in id_table and I need to run this procedure for at least how many rows in table1. I am using while loop to run loop till counts finish in table1 but can anyone tell me how can I change @ID every time.
If anyone can tell me how to do in c# will also be fine.
declare @ID INT declare @noRun1 INT declare @howTime INT set @noRun1=1 set @howTime = (select count(*) from table1) set @ID =(select top 1 id from id_table) while (@noRun1<=@howTime) begin EXEC proc_run @ID set @noRun1=@noRun1+1 end
2 Solutions collect form web for “Run Stored Procedure with table data as parameter”
DECLARE @uniqueId int DECLARE @TEMP TABLE (uniqueId int) -- Insert into the temporary table a list of the records to be updated INSERT INTO @TEMP (uniqueId) SELECT uniqueId FROM myTable -- Start looping through the records WHILE EXISTS (SELECT * FROM @TEMP) BEGIN -- Grab the first record out SELECT Top 1 @uniqueId = uniqueId FROM @TEMP PRINT 'Working on @uniqueId = ' + CAST(@uniqueId as varchar(100)) -- Perform some update on the record EXEC proc_run @uniqueId -- Drop the record so we can move onto the next one DELETE FROM @TEMP WHERE uniqueId = @uniqueId END
So you want to execute a stored procedure for each id in your table?
Rewrite your selection of id so that you can skip a number of rows. Something like this:
while (@noRun1 <= @howTime) begin select @ID = id from (select id, (ROW_NUMBER() over (order by id)) as numrow from id_table) as tab where numrow = @noRun1 EXEC proc_run @ID set @noRun1 = @noRun1 + 1 end
If you are using SQL Server 2008+ you can rewrite your stored procedure to accept table-valued parameters, pass the whole list of ids and only execute once. Check out this example: http://technet.microsoft.com/en-us/library/bb510489.aspx