PRINT Messages before executing a SP

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

PRINT Messages before executing a SP

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-08-01 : 20:01:02

Hi,

I have a SP called Extraction_Info that calls other Stored Procedures(Create_Employee, Create_Detail).

When i execute the SP Extraction_Info, I want to print messages before execution of individual
Stored procedures within this SP.

Code that i currently use:

=================================================================
CREATE PROC Extraction_Info
AS

BEGIN
PRINT '** Employee Extraction ..In progress **'
EXEC Create_Employee
PRINT '** Employee Extraction .. Completed **'
END
.
.
BEGIN
PRINT '** Detail Extraction ... In progress **'
EXEC Create_Detail
PRINT '** Detail Extraction ... Completed **'
END
===================================================================

The problem is that the message '** Employee Extraction ..In progress **' gets printed only after the execution of the proc Create_Employee is completed

Please advise how i should change the code.
Thanks,

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-08-01 : 20:12:56

I had also SET NOCOUNT ON to suppress line messages in the proc.. Not sure if this could create the problem.

CREATE PROC Extraction_Info
AS

BEGIN
SET NOCOUNT ON
END
BEGIN
PRINT '** Employee Extraction ..In progress **'
EXEC Create_Employee
PRINT '** Employee Extraction .. Completed **'
END
.
.
BEGIN
PRINT '** Detail Extraction ... In progress **'
EXEC Create_Detail
PRINT '** Detail Extraction ... Completed **'
END

sbalaji
Starting Member

48 Posts

Posted - 2007-08-02 : 08:23:32

Are u getting output like this
** Employee Extraction .. Completed **
** Employee Extraction ..In progress **

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-02 : 13:18:01

I'm not sure if you can do anything about the print not showing up in a timely manner. But, I know that you can use RAISEERROR to accomplish this:

RAISERROR('Insert Message Here', 0, 1) WITH NOWAIT

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-08-02 : 13:18:33

When i run the proc the Create_Employee gets executed and after that
** Employee Extraction .. Completed **
** Employee Extraction ..In progress **
gets displayed simultaneously.

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-08-02 : 13:24:05

I tried the RAISEERROR command to print the messages.. but still there is a time lag and it prints after executing the proc.

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2007-08-02 : 14:23:58

Hi,
I created a temporary table and updated the status in that table before executing the SP and after executing the SP. Now i read that table for status.. I guess i'll use this roundabout method to overcome this problem.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources