Using multiple relating statements inside a stored procedure?

Question

I am using TSQL. I found this stored procedure template in the internet.

I want to first create a table A and then create from table a the table C. Would this work?

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 

AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
      BEGIN TRAN
        SELECT Name into table_A From table1
      COMMIT TRAN

      BEGIN TRAN
         SELECT Name into table_C From table_A
      COMMIT TRAN

   END TRY
   BEGIN CATCH
      IF @@TRANCOUNT > 0
         ROLLBACK TRAN
      DECLARE @ErrorMessage NVARCHAR(4000);
      DECLARE @ErrorSeverity INT;
      DECLARE @ErrorState INT;
 
      SELECT @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState );
   END CATCH
END
GO

Though, side note, in *new* development work you should be using `THROW`, not `RAISERROR`; as noted in the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver15). — Thom A, Sep 29 '21 at 11:31
This guy used it https://www.mssqltips.com/sqlservertip/6588/sql-server-stored-procedure-custom-templates-ssms-and-visual-studio/. he is a Pro — lottikkk, Sep 29 '21 at 11:32
A "pro" are they? Then you should ask them why they aren't following the advise that has been on the documentation for *at least* 9 years... Also, don't just blindly copy pasta code you find on the internet; take the time to understand it. Learn about the syntax. There are *so* many examples of bad code out there (and I don't doubt some will be from myself in my "younger"/"less experienced" days). Just blindly copy pastaing code, and not bothering to learn what code *actually* does, is how problems like SQL Injection is *still* an issue now, when it should have died out in the 90's — Thom A, Sep 29 '21 at 11:35
I get an error if I use RAISERROR(ErrorMessage, ErrorSeverity, ErrorState ); — lottikkk, Sep 29 '21 at 11:37
That's off topic for [so]. If you have a problem with the above code, then explain that in the question. All you ask right now is *"Would this work?"* and you don't need *us* to answer that question, you can do it yourself; paste the code into your IDE, run it, then execute the procedure and see what happens. — Thom A, Sep 29 '21 at 11:41

score 1 · Accepted Answer · answered Sep 29 '21 at 13:38

FIRST - there is no need to write BEGIN TRANSACTION and COMMIT arround all SQL Statement that manipulate data or structures inside the database. SQL Server run in autocommit mode, which mean that every SQL statement (INSERT, DELETE, UPDATE, CREATE, ALTER...) has it own transacion scope. If you want to have a transaction involving many statement use only one transaction...

SECOND - The syntax of RAISERROR does not accept parameters for then 2nd and 3rd argument as shown in the doc (just press F1 on the command when in SSMS) :

RAISERROR ( { msg_id | msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n

As you see, only the message can be argued as a local variable (it is why you get the error...) Severity must be 16 which is the reserved class of developper error raising. 1 is the default state, but this is no more used...

THIRD - because the transaction is not systematically began whe entering in the CATCH part, you need to test if the transaction is alive or not with the XACT_STATE() function

FOURTH - it is preferable to use THROW instead of RAISERROR as the doc says...

FITH - Instead of writing separates command DECLARE and a SELECT for assignement, you can declare and set simultaneously

SIXTH - terminate all your TSQL sentences with a semicolon

Well now you have the code :

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
      BEGIN TRANSACTION;
        SELECT Name into table_A From table1;
        SELECT Name into table_C From table_A;
      COMMIT TRAN;
   END TRY
   BEGIN CATCH
      IF XACT_STATE() <> 0
         ROLLBACK TRAN;
      DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE(); 
      THROW 55555, @ErrorMessage, 1;
   END CATCH
END
GO

Thank you SQLpro. Why we need XACT_STATE? I wrote above already "commit Tran" and End TRY. That end all Transaction, why we need XACT_STATE? — lottikkk, Sep 29 '21 at 18:59
of course you can drop the IF XACT_STATE() ... but if you add some code before or after the frontiers of the transaction, you do not need to ROLLBACK. It is why I ever tests the transactional state. XACT_STATE() returns 0 if there is no explicit transaction and 1 if the transaction is alive and -1 if it is alive with an error. — SQLpro, Sep 30 '21 at 06:58

score 0 · Answer 2 · answered Sep 29 '21 at 13:27

0

It will definitely work. Stored Procedure allows you to have multiple statement (create table, insert, update, index) and many more.

answered Sep 29 '21 at 13:27

Lenroy Yeung

291
3
8

score 0 · Answer 3 · answered Sep 29 '21 at 21:52

The best way to handle exceptions in SQL is, oddly enough, to not handle them at all.

If you catch the exception, and there are multiple messages, you lose that info. You also prevent the exception from bubbling up properly to the client. It only looks like a strange SELECT.

Furthermore, you can't catch all exceptions anyway. There are a number of fatal exceptions which are un-catchable.

So the best thing to do is to just use XACT_ABORT ON to ensure that any transactions are always rolled back and not left hanging. Note that not every query needs a transaction, only if you want to do multiple things atomically.

You should also use NOCOUNT ON for performance reasons

CREATE OR ALTER Procedure_Name
AS

SET XACT_ABORT, NOCOUNT ON;

SELECT 'Something';

GO

CREATE OR ALTER Procedure_Name
AS

SET XACT_ABORT, NOCOUNT ON;

BEGIN TRAN;

  UPDATE SomeTable....;

  INSERT SomeTable....;

COMMIT;

GO

Using multiple relating statements inside a stored procedure?

3 Answers3

Linked