CODEWITHSUNDEEP
sql-serversql-server-2008
ONYX
ONYX

Reputation: 5859

SQL Transaction Duplicate Entry

This is the first time i've used IF Exists and BEGIN TRANSACTION. I get an error on 'incorrect syntax near ')' in the If Exists part and incorrect syntax near 'Transaction' at the end of End Transaction. What I'm trying todo is raise an error if a duplicate Entry exists and if there is an error rollback the transaction and return the error or return @@RowCount i don't know which one I should choose could someone correct my procedure and make any adjustments on it please.

BEGIN TRANSACTION
IF EXISTS (SELECT * FROM Forums WHERE Title = @Title) 
    BEGIN
        RAISERROR ('Duplicate Entry', 16, 1)
    END
ELSE
    BEGIN   
        INSERT INTO Forums(AddedBy, AddedDate, Title, Description, 
               ParentID, Moderated, ImageUrl, UpdatedBy, UpdatedDate, Active, Importance)
        VALUES(@AddedBy, @AddedDate, @Title, null, null, False, null, null, null, True, 0)
        RETURN @@ROWCOUNT
    END


IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION
        RETURN @@ERROR
    END

END TRANSACTION

Upvotes: 0

Views: 2802

Answers (3)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

You don't need a transaction unless you wanna enforce an isolation level, or if you have more than one Update/Insert/Delete statement, and you need to roll them all back if an error happens, or commit them all if everything is successful,

in your sample code, you have an insert statement that won't even get to execute, so you have nothing to rollback

DECLARE @InsertedRows INT = 0

INSERT INTO Forums(AddedBy, AddedDate, Title, Description, ParentID, 
    Moderated, ImageUrl, UpdatedBy, UpdatedDate, Active, Importance)
SELECT @AddedBy, @AddedDate, @Title, null, null, 'False', null, null, null, 'True', 0
WHERE NOT EXISTS (SELECT * FROM Forums WHER Title = @Title)


SET @InsertedRows = @@ROWCOUNT

IF @InsertedRows = 1 
  RETURN 1
ELSE
  RETURN -1

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You don't really need the transaction. A single-row insert is either going to succeed or fail (and @@ROWCOUNT is only every going to return 1 in this case). And if you have a unique constraint on the title column (which you should have, apparently), you don't need to check first, either, though it may be a bit more efficient to do so (I'll have to perform some tests to confirm, but I'm pretty sure the error mechanism is pretty costly). Anyway here is a version with error handling in case something should go wrong other than a constraint violation on the title:

IF EXISTS (SELECT 1 FROM dbo.Forums WHERE Title = @Title)
BEGIN
    RAISERROR('Duplicate entry', 16, 1);
    RETURN 2627; -- key constraint violation
END
ELSE
BEGIN
    BEGIN TRY
        INSERT dbo.Forums
        (
          AddedBy, AddedDate, Title, 
          Description, ParentID, 
          Moderated, ImageUrl, UpdatedBy, 
          UpdatedDate, Active, Importance
        )
        SELECT
          @AddedBy,
          @AddedDate,
          @Title, 
          NULL, NULL, 
          'False', -- did you mean 0?
          NULL, NULL, NULL,
          'True', -- did you mean 1?
          0;

        RETURN 1;
    END TRY
    BEGIN CATCH
        DECLARE @msg NVARCHAR(4000) = 'Insert failed ' + ERROR_MESSAGE();
        RAISERROR(@msg, 16, 1);
        RETURN ERROR_NUMBER();
    END CATCH
END

Upvotes: 0

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3438

BEGIN TRANSACTION

    IF EXISTS (SELECT * FROM Forums WHERE Title = @Title) 
    BEGIN
        RAISERROR ('Duplicate Entry', 16, 1)
    END
    ELSE
    BEGIN   
          INSERT INTO Forums(AddedBy, AddedDate, Title, Description, ParentID, 
              Moderated, ImageUrl, UpdatedBy, UpdatedDate, Active, Importance)
          VALUES(@AddedBy, @AddedDate, @Title, null, null, False, null, null, null, True, 0)

          COMMIT TRANSACTION

        RETURN @@ROWCOUNT
    END

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION
        RETURN @@ERROR
    END

Upvotes: 0

Related Questions