SQL Agent job to select a value from table and pass the values as input parameter to execute stored procedure

Question

I have a stored procedure which expects integer as input parameter. i need this sp to run every 10 mins. for this im thinking to use sql server agent job.

i want to select a set of values from table.column and pass this as an input parameter to the stored procedure every 10 minutes.

Appreciate your assistance in advance.

Thanks & Regards, Sanjai

Answer 1

If I have understand your requirement.

Use Cursor (if the set of values are not huge) to pass the values from set of values of table.

DECLARE @value VARCHAR(50) -- table.column value

DECLARE myCursor CURSOR FOR  
SELECT ColVal FROM Table -- Get the values from table

OPEN myCursor   
FETCH NEXT FROM myCursor INTO @value   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       EXEC dbo.getValue @value  -- Execute the SP
       FETCH NEXT FROM myCursor INTO @value   
END   

CLOSE myCursor   
DEALLOCATE myCursor

Use this T-SQL in agent job step.