question

BI new avatar image
BI new asked

nested stored procedures

How do you commit or rollback nested stored procedures? I was asked this question in an interview. My answer was using a transaction. But the interviewer says it is incorrect.
stored-procedures
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered
harsh...you were mostly correct CREATE PROC p1 AS SET NOCOUNT ON BEGIN TRAN BEGIN TRY EXEC p2 COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN END CATCH CREATE PROC p2 AS BEGIN TRY DELETE FROM T1 SELECT 1/0 END TRY BEGIN CATCH RAISERROR (50001,11,1) END CATCH
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.