Transaction Sql Server Stored Procedure

1. Stored Procedure W3schools

I need to perform an UPDATE and an INSERT in a single transaction. That code works fine on its own, but I'd like to be able to call it easily and pass in the required parameters. When I try to nest this transaction in a stored procedure I run into lots of syntax errors.

Using Transact-SQL. To create a procedure in Query Editor. In Object Explorer, connect to an instance of Database Engine. From the File menu, click New Query. Copy and paste the following example into the query window and click Execute.This example creates the same stored procedure as above using a different procedure name. System Stored Procedures (Transact-SQL); 4 minutes to read; Contributors. In this article. APPLIES TO: SQL Server (starting with 2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse In SQL Server 2017, many administrative and informational activities can be performed by using system stored procedures. From SQL Server (not sure about other RDBMS), You can call multiple stored procedures inside a transaction. BEGIN TRAN EXEC StoredProc1 EXEC StoredProc2 COMMIT TRAN You may want to add a return code to the stored proc to check if you should run stored proc 2 if stored proc 1 failed. EDIT: To check a return code you can do something like the.

How can I encapsulate the following code so it can be easily called?

Max Vernon

2 Answers

You like need to wrap that code in CREATE PROCEDURE .. syntax, and remove the GO statements after BEGIN TRANSACTION and before COMMIT TRANSACTION.

Also note, I have added a TRY..CATCH statement block to allow performing a ROLLBACK TRANSACTION statement in case some error occurs. You probably need better error handling than that, but without knowledge of your requirements, that is difficult at best.

Some good reading:

Max VernonMax Vernon

If you want to properly handle nested Stored Procedures that can handle Transactions (whether started from T-SQL or app code) then you should follow the template that I described in the following answer:

You will notice two differences there from what you are attempting here:

1. The use of RAISERROR within the CATCH block. This bubbles the error up to the calling level (whether in the DB or app layer) so a decision can be made regarding the fact that an error occurred.

2. No SAVE TRANSACTION. I have never found a case for using this. I know some folks prefer it, but in everything I have ever done at any place that I have worked, the notion of an error occurring within any of the nested levels implied that whatever work was already done was invalid. By using SAVE TRANSACTION you are only reverting back to the state just prior this Stored Procedure being called, leaving the existing process as otherwise valid.

   If you want more details on SAVE TRANSACTION, then please take a look at the information in this answer:

   Another issue with SAVE TRANSACTION is a nuance of its behavior, as noted in the MSDN page for SAVE TRANSACTION (emphasis added):

   Duplicate savepoint names are allowed in a transaction, but a ROLLBACK TRANSACTION statement that specifies the savepoint name will only roll the transaction back to the most recent SAVE TRANSACTION using that name.

   Meaning, you need to be very careful to give each Save Point in each Stored Procedure a name that is unique across all Save Points in all Stored Procedures. The following examples illustrate this point.

   This first example shows what happens when you reuse the Save Point name; only the lowest-level Save Point is rolled-back.

   This second example shows what happens when you use unique Save Point names; the desired level's Save Point is rolled-back.

Solomon RutzkySolomon Rutzky

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2012t-sqlstored-procedurestransaction or ask your own question.

I have a sql script that is set to roll to production. I've wrapped the various projects into separate transactions. Fate hollow ataraxia pc. In each of the transactions we created stored procedures. I'm getting error messages

Msg 156, Level 15, State 1, Line 4Incorrect syntax near the keyword 'procedure'.

I created this example script to illustrate

The error seems to imply that I can't create stored procs inside of transaction, but I'm not finding any docs that say otherwise(maybe google isn't being freindly today).

Factor MysticChris LChris L

4 Answers

try doing the create procedure in EXEC('..'), like this:

KM.KM.

Stored Procedure W3schools

You cannot write your script in this way as there are many statements that must be run in their own batch. Instead, I would recommend doing something akin to how Red-Gate's SQL Compare builds its scripts:

ThomasThomas

There are various issues with the solution proposed by KM:

• If the content of what you put in your EXEC() call is incorrect semantically (for example you put a non-existant table in the FROM inside the stored procedure) that error doesn't bubble up and the transaction is not rolled back.

• If the content of what you put in your EXEC() call is incorrect syntactically (for example you put SELECTT instead of SELECT inside the stored procedure), the transaction seems to be rolled back but a completely cryptic error bubbles up:

So I'm still at a loss to how to create a procedure inside a transaction but still have the transaction and the try-catch be useful.

I seem to recall you cannot do things like create, modify or drop database schema objects—including stored procedures—inside a transaction (since such structural changes are not transactional: you cannot roll them back with data changes).

RichardRichard

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2008stored-procedures or ask your own question.