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
try this
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
