Using multiple relating statements inside a stored procedure?-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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