schedule sql server stored procedures defined in a table??

Re: schedule sql server stored procedures defined in a table??


You can work something like this. Here are my ideas.......... .


Create a "holding" table for each stored procedure.
What I mean by this is that this is where your parmeters can do for a stored
procedure.

Let's say you needed to call a procedure called
uspUpdateEmploy eeHistory

instead of having

uspUpdateEmploy eeHistory (empid int)

just have it as

uspUpdateEmploy eeHistory
......

Ok.

Then create a "holding table"

create table UpdateEmployeeH istoryHoldingTa ble (empid int)

.......
Ok
uspUpdateEmploy eeHistory is smart enough to look in the holding table for
parameters.

..........

Ok next step, create a

StoredProcedure sToRun table

create table StoredProcedure sToRun ( uspname varchar(128) )

........

whenever you need a job to run, you just pop an entry into this table.

......

Finally, you schedule 1 job. I guess to run every 1 minute for example.

You also have a stored procedure. called
uspCheckJobToRu nTableAndRunJob


.......

the schecued job will always call uspCheckJobToRu nTableAndRunJob when it
runs.
uspCheckJobToRu nTableAndRunJob contains code to look at the
StoredProcecurs ToRunTable

then using dynamic sql, you can call

declare @usp varchar(128)
select @usp = top 1 uspname from StoredProcecurs ToRunTable

if (@usp IS NOT NULL)
begin
EXEC ( @usp )
end


You'll have to write a cursor (ahhhhh?) to handle the multi records in
StoredProcecurs ToRunTable

.......


Ok..

Finally, you want something to fire.

When you actually need an employee history to update , you do this:


INSERT INTO UpdateEmployeeH istoryHoldingTa ble (empid ) values 1001
INSERT INTO StoredProcedure sToRun (uspname) values
'uspUpdateEmplo yeeHistory'


.......

Then, when the job goes off 1 to 60 seconds later (because it fires every
minute).......

eventually

EXEC 'uspUpdateEmplo yeeHistory' will run (via the EXEC ( @usp ) above)

and remembering that
'uspUpdateEmplo yeeHistory' is smart enough to find the "1001" value in the
UpdateEmployeeH istoryHoldingTa ble table.


Kinda hacky, but it'll get the job done.


PS
I like the "holdingtab le" idea, because then you're not screwed if you need
to go to 2 parameters for each stored procedure.
And, if you get duplicates in your holding table, its not a big idea.
Like if 1001 (empid) got added to the UpdateEmployeeH istoryHoldingTa ble 55
times, you only need to run it once.
select distinct empid from UpdateEmployeeH istoryHoldingTa ble


Make any sense?


I call this poor man's async processing.






When you need to update the history of any employee
"jobs" <jobs@webdos.co mwrote in message
news:1169124329 .902470.259470@ 51g2000cwl.goog legroups.com...