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.
How can I encapsulate the following code so it can be easily called?
BEGIN TRANSACTION AssignUserToTicket
GO
DECLARE @updateAuthor varchar(100)
DECLARE @assignedUser varchar(100)
DECLARE @ticketID bigint
SET @updateAuthor = 'user1'
SET @assignedUser = 'user2'
SET @ticketID = 123456
UPDATE tblTicket SET ticketAssignedUserSamAccountName = @assignedUser WHERE (ticketID = @ticketID);
INSERT INTO [dbo].[tblTicketUpdate]
([ticketID]
,[updateDetail]
,[updateDateTime]
,[userSamAccountName]
,[activity])
VALUES
(@ticketID,
'Assigned ticket to ' + @assignedUser,
GetDate(),
@updateAuthor,
'Assign');
GO
COMMIT TRANSACTION AssignUserToTicket
Best Answer
You need to wrap that code in
CREATE PROCEDURE ...
syntax, and remove theGO
statements afterBEGIN TRANSACTION
and beforeCOMMIT TRANSACTION
.Also note, I have added a
TRY...CATCH
statement block to allow performing aROLLBACK 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:
Always specify the schema
Stored Procedure Best Practices
Bad habits to avoid