Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL - create a stored proc inside a transaction statement

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

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

I created this example script to illustrate

Begin Try
Begin Transaction 
    -- do a bunch of add/alter tables here
    -- do a bunch of data manipulation/population here

    -- create a stored proc
  create procedure dbo.test
  as
  begin
    select * from some_table
  end
Commit  
End Try
Begin Catch
    Rollback  
    Declare @Msg nvarchar(max)
    Select @Msg=Error_Message();
    RaisError('Error Occured: %s', 20, 101,@Msg) With Log;
End Catch

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).

like image 670
Chris L Avatar asked Apr 26 '10 16:04

Chris L


People also ask

Can I use a stored procedure in a select statement?

We can not directly use stored procedures in a SELECT statement.

Do stored procedures run in a transaction?

There will only be one connection, it is what is used to run the procedure, no matter how many SQL commands within the stored procedure. since you have no explicit BEGIN TRANSACTION in the stored procedure, each statement will run on its own with no ability to rollback any changes if there is any error.

Can I execute stored procedure inside function?

You cannot execute a stored procedure inside a function, because a function is not allowed to modify database state, and stored procedures are allowed to modify database state.

Can we use transaction in stored procedure SQL Server?

Yes, a stored procedure can be run inside a transaction.


1 Answers

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

Begin Try
Begin Transaction 
    -- do a bunch of add/alter tables here
    -- do a bunch of data manipulation/population here

    -- create a stored proc
  EXEC ('create procedure dbo.test
  as
  begin
    select * from some_table
  end')
Commit  
End Try
Begin Catch
    Rollback  
    Declare @Msg nvarchar(max)
    Select @Msg=Error_Message();
    RaisError('Error Occured: %s', 20, 101,@Msg) With Log;
End Catch

GO
like image 83
KM. Avatar answered Oct 20 '22 21:10

KM.