status execution Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

status execution Stored procedure

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2010-07-22 : 12:56:59

Hi All.

I have SP that execute 6 procedures. I would like to to create SP which return me status of process execution. What I mean, for instance, table will display what SP or SPs now executing, time of thier execution , and when procedure finish execution will indicate by "Complete". If it possible how it to do?

Thanks.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-22 : 13:10:53

to give you a start:


create procedure dbo.SP_getdate1
as
	select getdate()
	print 'SP_getdate1'

create procedure dbo.SP_getdate2
as	
	waitfor delay '00:00:05'
	select getdate()
	print 'SP_getdate2'



create procedure dbo.SP_getdate3
AS	
	waitfor delay '00:00:05'
	select getdate()
	print 'SP_getdate3'



create procedure dbo.SP_getdate_ALL
as
	execute dbo.SP_getdate1
	print 'ALL-SP_getdate1-success'
	execute dbo.SP_getdate2
	print 'ALL-SP_getdate2-success'
	execute dbo.SP_getdate3
	print 'ALL-SP_getdate3-success'
	
execute dbo.SP_getdate_ALL


drop procedure dbo.SP_getdate_ALL
drop procedure dbo.SP_getdate1
drop procedure dbo.SP_getdate2
drop procedure dbo.SP_getdate3

you can replace all the print '' statements with
INSERT INTO MyTable
select getdate(), 'SP on Success', 'Step:2'
etc.
you can also use RAISEERROR command with begin-end try and begin-end catch block.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-22 : 14:24:13

You might want to do some research for Auditing Framework since that sounds like what you are trying to do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources