Make your own free website on Tripod.com
Transactions in T-SQL [Home]

To see how to use transactions in T-SQL let see the following example:

CREATE PROCEDURE spAddBookmark
 @UserID AS INT,
 @Comment AS VARCHAR (100) = NULL,
 @GroupID AS INT = NULL,
 @ID AS INT OUTPUT
AS

DECLARE @Bookmarks INT
DECLARE @err INT

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

BEGIN TRANSACTION

INSERT INTO Bookmark (UserID, Comment) VALUES (@UserID, @Comment)

SET @err = @@ERROR
IF @err <> 0
 BEGIN
  ROLLBACK TRANSACTION
  RETURN
@err
 END
ELSE
 BEGIN
  SELECT
@ID = @@IDENTITY
 END

--if bookmark must be shared with members of the same group (this means @GroupID is not NULL)

IF @GroupID IS NOT NULL
 BEGIN
   INSERT INTO
BookmarkSharing (BookmarkID, GroupID) VALUES (@ID, @GroupID)
   SET @err = @@ERROR
   IF @err <> 0
     BEGIN
       ROLLBACK TRANSACTION
       RETURN
@err
     END
 END
COMMIT TRANSACTION

SET NOCOUNT OFF
SET ANSI_WARNINGS ON
GO