Run stored procedure from sql job based on select results

Question

I have a sql table with four fields a,b,c,d and a stored procedure sp_x.

How can I create a job that will run periodically and do two things (with one select):

update field c and field d for rows that have b=1;
And after that execute sp_x passing it field a but only for rows that have b=1

Watch the post : [http://stackoverflow.com/questions/2348863/how-to-run-a-stored-procedure-in-sql-server-every-hour][1] [1]: http://stackoverflow.com/questions/2348863/how-to-run-a-stored-procedure-in-sql-server-every-hour — Metaphor, Sep 09 '15 at 08:15
"with one select" why? What is the problem with 2 or more? What is the actual goal? (other than running it periodically) — Pred, Sep 09 '15 at 08:22
b is an indication of all rows that have expired (the actual check is not b=1 but actually b — user1480192, Sep 09 '15 at 08:35
The reason for only on select is that the update needs to happen first and after field c is updated to a canceled status the select will not pick it up a second time. — user1480192, Sep 09 '15 at 08:36
You can use `OUTPUT` as @EdwardComeau mentioned in his answer, or you can add a `AFTER UPDATE` trigger to the table which logs the events what you want. — Pred, Sep 09 '15 at 09:50

Edward Comeau · Accepted Answer · 2015-09-09T09:32:52.707

You could use "output" of the update statement to receive the row id of the columns you have just updated and write this data into a temporary table or table variable.

This then allows more statements to be used to process the logging activity.

You could also consider using on update triggers on your table that perform the logging when your status changes.

use [tempdb];
go

create table [outputexample]
(
      [rowid] int identity(1,1)
    , [datetime] datetime default (getdate()) not null
    , [status] varchar(20) not null
);

insert into [outputexample] ([status])
select 'Active' union all
select 'Closed' union all
select 'Active'
go

Scheduled job code:

declare @updatedrow table
(
    [rowid] int
);

update [outputexample]
set [datetime] = getdate()
    , [status] = 'Closed'
output inserted.[rowid]
into @updatedrow
where [datetime] < getdate()
    and [status] <> 'Closed';


declare @rowid int;

declare cursor_updatedrow cursor for
select [rowid] from @updatedrow;

open cursor_updatedrow;

fetch next from cursor_updatedrow into @rowid;

while @@fetch_status = 0
begin

    -- exec sp_myauditsp @rowid = @rowid
    print cast(@rowid as varchar(20)) + ' was updated to closed.'

    fetch next from cursor_updatedrow into @rowid;
end

close cursor_updatedrow;
deallocate cursor_updatedrow;

score 0 · Answer 2 · answered Sep 09 '15 at 09:18

updating the rows you want:

UPDATE mytable SET C=..., D=... WHERE B=1

executing stored proc for certain rows only:

DECLARE @a int
DECLARE my_cursor CURSOR
FOR SELECT a FROM jobtable WHERE b=1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @a
WHILE @@FETCH_STATUS=0
BEGIN 
  EXEC sp_x @a
END
FETCH NEXT FROM my_cursor into @a
CLOSE my_cursor
DEALLOCATE my_Cursor

All inside a job that runs when you want

Run stored procedure from sql job based on select results

2 Answers2