CODEWITHSUNDEEP
sqlsql-server
chinghp
chinghp

Reputation: 117

Execute SQL Server stored procedures sequentially with notification

For Microsoft SQL server, I would like to execute stored procedures sequentially, with notification and using results when later parts of procedures are still executing. For example, the below code would print the 3 sentences, 'Update_TableX_done' only after all 3 procedures are completed.

'Update_TableX' are stored procedures that would insert some updated data into in a table.

Can I set such that Print is performed after each EXEC statement completed?

Can I use result of Table1 when Table2 is updating?

Can I keep the result of Update_Table1, if the process of Update_Table2 fails?

Or, is the only solution to manually start Update_Table2 only after Update_Table1 is finished?

    EXEC [dbo].[Update_Table1]
    PRINT 'Update_Table1_done'
    EXEC [dbo].[Update_Table2]
    PRINT 'Update_Table2_done'
    EXEC [dbo].[Update_Table3]
    PRINT 'Update_Table3_done'

Upvotes: 0

Views: 335

Answers (1)

Jacek Wróbel
Jacek Wróbel

Reputation: 1222

PRINT command flushes messages when a buffer is filled. To trigger immediate message flush to Message stream use RAISEERROR with WITH NOWAIT option.

raiserror('Test 1', 10, 1) with nowait;
waitfor delay '00:00:05';
raiserror('Test 2', 10, 1) with nowait;

Upvotes: 1

Related Questions