none
How to conditionally increment a field before insert

    Question

  • I have a table of document names that has a field for revision number (Rev)

    Before I do an insert I would like to determine if the document exists.

    If not, I want to set the Rev = 0 when I do the insert.

    Otherwise I want to get the existing rev for that document and increment it before the insert.

    My attempt is shown below but I am getting syntax error 'Incorrect syntax near @Rev'

    Also I wasn't sure whether to put SELECT SCOPE_IDENTITY() before or after Commit Transaction

    Thanks for assistance

    CREATE PROCEDURE dbo.procDealDocumentInsert2 ( @DealKey int, @DocumentName nvarchar(75) ) AS BEGIN TRANSACTION T1 DECLARE @Rev INT = 0 IF EXISTS (SELECT Rev FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName) @REV=Rev + 1 INSERT INTO tblDealDocuments ( DealKey, DocumentName, Rev ) VALUES ( @DealKey, @DocumentName, @Rev ) COMMIT TRANSACTION T1 SELECT SCOPE_IDENTITY()



    Steve Greenbaum

    Monday, February 20, 2012 2:43 PM

Answers

  • Steve, As you mentioned that if existing rev for that document and increment it before the insert.

    CREATE PROCEDURE dbo.procDealDocumentInsert2
    (
    @DealKey			int,
    @DocumentName		nvarchar(75)
    )	
    AS
    
    BEGIN TRANSACTION T1
    DECLARE @Rev INT = 0
    
    
    IF EXISTS (SELECT Rev FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName)
    Begin
     SELECT @Rev=Rev FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName
     SET @REV=@Rev + 1
    End
    
    INSERT INTO tblDealDocuments
    (
    DealKey,	
    DocumentName,	
    Rev
    )	
    VALUES
    (
    @DealKey,		
    @DocumentName,	
    @Rev
    )	
    COMMIT TRANSACTION T1
    SELECT SCOPE_IDENTITY()


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by steve48 Monday, February 20, 2012 3:23 PM
    Monday, February 20, 2012 2:57 PM
  • Try this.

    CREATE PROCEDURE dbo.procDealDocumentInsert2
    
    (
    @DealKey			int,
    @DocumentName		nvarchar(75)
    )	
    AS
    
    BEGIN TRANSACTION T1
    DECLARE @Rev INT 
    SET @Rev = 0
    
    IF EXISTS 
    (SELECT Rev FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName)
    
    SELECT @REV=Rev + 1 FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName
    
    INSERT INTO tblDealDocuments
    (
    DealKey,	
    DocumentName,	
    Rev
    )	
    VALUES
    (
    @DealKey,		
    @DocumentName,	
    @Rev
    )	
    COMMIT TRANSACTION T1
    SELECT SCOPE_IDENTITY()


    Changed it to 2005 compatible.

    SCOPE_IDENTITY() works for the the same scope. I think it should work.

    BOL:Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

    Check and let me know.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Proposed as answer by David Frommer Monday, February 20, 2012 3:13 PM
    • Marked as answer by steve48 Monday, February 20, 2012 3:23 PM
    Monday, February 20, 2012 3:05 PM
  • Tty this.

    CREATE PROCEDURE dbo.procDealDocumentInsert2 ( @DealKey int, @DocumentName nvarchar(75) ) AS BEGIN TRANSACTION T1 DECLARE @Rev INT = 0 SET @REV= (SELECT case when isnull(Rev,0)=0 then 0 else Rev+1 end

    FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName)

    INSERT INTO tblDealDocuments ( DealKey, DocumentName, Rev ) VALUES ( @DealKey, @DocumentName, @Rev ) COMMIT TRANSACTION T1 SELECT SCOPE_IDENTITY()

    scope_identity() position is fine.you may keep it after the commit transaction.

    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    • Edited by Vinay Valeti Monday, February 20, 2012 3:09 PM
    • Marked as answer by steve48 Monday, February 20, 2012 3:24 PM
    Monday, February 20, 2012 3:05 PM

All replies

  • Steve, As you mentioned that if existing rev for that document and increment it before the insert.

    CREATE PROCEDURE dbo.procDealDocumentInsert2
    (
    @DealKey			int,
    @DocumentName		nvarchar(75)
    )	
    AS
    
    BEGIN TRANSACTION T1
    DECLARE @Rev INT = 0
    
    
    IF EXISTS (SELECT Rev FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName)
    Begin
     SELECT @Rev=Rev FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName
     SET @REV=@Rev + 1
    End
    
    INSERT INTO tblDealDocuments
    (
    DealKey,	
    DocumentName,	
    Rev
    )	
    VALUES
    (
    @DealKey,		
    @DocumentName,	
    @Rev
    )	
    COMMIT TRANSACTION T1
    SELECT SCOPE_IDENTITY()


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by steve48 Monday, February 20, 2012 3:23 PM
    Monday, February 20, 2012 2:57 PM
  • Try this.

    CREATE PROCEDURE dbo.procDealDocumentInsert2
    
    (
    @DealKey			int,
    @DocumentName		nvarchar(75)
    )	
    AS
    
    BEGIN TRANSACTION T1
    DECLARE @Rev INT 
    SET @Rev = 0
    
    IF EXISTS 
    (SELECT Rev FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName)
    
    SELECT @REV=Rev + 1 FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName
    
    INSERT INTO tblDealDocuments
    (
    DealKey,	
    DocumentName,	
    Rev
    )	
    VALUES
    (
    @DealKey,		
    @DocumentName,	
    @Rev
    )	
    COMMIT TRANSACTION T1
    SELECT SCOPE_IDENTITY()


    Changed it to 2005 compatible.

    SCOPE_IDENTITY() works for the the same scope. I think it should work.

    BOL:Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

    Check and let me know.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Proposed as answer by David Frommer Monday, February 20, 2012 3:13 PM
    • Marked as answer by steve48 Monday, February 20, 2012 3:23 PM
    Monday, February 20, 2012 3:05 PM
  • Tty this.

    CREATE PROCEDURE dbo.procDealDocumentInsert2 ( @DealKey int, @DocumentName nvarchar(75) ) AS BEGIN TRANSACTION T1 DECLARE @Rev INT = 0 SET @REV= (SELECT case when isnull(Rev,0)=0 then 0 else Rev+1 end

    FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName)

    INSERT INTO tblDealDocuments ( DealKey, DocumentName, Rev ) VALUES ( @DealKey, @DocumentName, @Rev ) COMMIT TRANSACTION T1 SELECT SCOPE_IDENTITY()

    scope_identity() position is fine.you may keep it after the commit transaction.

    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer


    • Edited by Vinay Valeti Monday, February 20, 2012 3:09 PM
    • Marked as answer by steve48 Monday, February 20, 2012 3:24 PM
    Monday, February 20, 2012 3:05 PM
  • Here is one approach. This seem to be the only one caring for MAXIMUM rev number.

    CREATE PROCEDURE dbo.procDealDocumentInsert2
    (
    	@DealKey INT,
    	@DocumentName NVARCHAR(75)
    )	
    AS
    
    SET NOCOUNT ON
    
    MERGE	dbo.tblDealDocuments AS tgt
    USING	(
    		VALUES	(
    				@DealKey,
    				@DocumentName,
    				(SELECT MAX(Rev) FROM dbo.tblDealDocuments WHERE DealKey = @DealKey AND DocumentName = @DocumentName)
    			)
    	) AS src (DealKey, DocumentName, Rev) ON src.DealKey = tgt.DealKey
    		AND src.DocumentName = tgt.DocumentName
    WHEN	MATCHED
    		THEN	INSERT	(
    					DealKey,	
    					DocumentName,	
    					Rev
    				)
    			VALUES	(
    					src.DealKey,
    					src.DocumentName,
    					src.Rev + 1
    				)
    WHEN	NOT MATCHED BY TARGET
    		THEN	INSERT	(
    					DealKey,	
    					DocumentName,	
    					Rev
    				)
    			VALUES	(
    					src.DealKey,
    					src.DocumentName,
    					0
    				)
    OUTPUT	tgt.$IDENTITY$;


    N 56°04'39.26"
    E 12°55'05.63"

    Monday, February 20, 2012 3:34 PM
  • Thank you for your response. You are correct. I need to get the Max.

    I ended up using the approach suggested by Manish_BI only because it seemed more straight forward to me.

    Your approach is very excellent also.

    What I ended up with is

    BEGIN TRANSACTION T1
    DECLARE @Rev INT = 0 
    
    
    IF EXISTS (SELECT Rev FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName)
    Begin
     SELECT @Rev=Max(Rev) FROM tblDealDocuments WHERE DealKey=@DealKey and DocumentName = @DocumentName
     SET @REV=@Rev + 1
    End
    


    Steve Greenbaum

    Monday, February 20, 2012 4:08 PM
  • Merge approach is safer for the concurrency.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 20, 2012 4:23 PM
  • Hi Niami,

    I thought transaction would make the approach safe. No?

    - Steve


    Steve Greenbaum

    Monday, February 20, 2012 4:29 PM
  • No really, I think. Check this excellent blog by Alex Kuznetsov

    http://sqlblog.com/blogs/alexander_kuznetsov/archive/2010/01/12/t-sql-tuesday-002-patterns-that-do-not-work-as-expected.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, February 20, 2012 4:34 PM
  • OK, I will use the Merge approach. Thanks for the advice

    Steve Greenbaum

    Monday, February 20, 2012 5:20 PM
  • Also, if you are using partitions, SCOPE_IDENTITY() can return a wrong value due to a bug.

    N 56°04'39.26"
    E 12°55'05.63"

    Monday, February 20, 2012 5:24 PM
  • When I tried to implement the merge approach I got an error message stating that

    'An action type of INSERT is not allowed in the MATCHED clause of a MERGE statement'


    Steve Greenbaum

    Monday, February 20, 2012 6:03 PM
  • Hi Steve,

    Thje error message clearly says the reason. You can only use UPDATE or DELETE in a MATCHED clause. The INSERT stataement should be in NOT MATCHED clause. Try to rewrite the earlier MERGE stataement to reverse the actions to include the UPDATE and INSERT statement.

    - Krishnakumar S

    Monday, February 20, 2012 6:44 PM
  • Unfortunately, I want to do an insert in both cases. I just want to conditionally set the Rev Value


    Steve Greenbaum

    Monday, February 20, 2012 7:22 PM
  • It's my fault. I should only post official code.

    CREATE PROCEDURE dbo.procDealDocumentInsert2
    (
    	@DealKey INT,
    	@DocumentName NVARCHAR(75)
    )	
    AS
    
    SET NOCOUNT ON
    
    INSERT	dbo.tblDealDocuments
    	(
    		DealKey,	
    		DocumentName,	
    		Rev
    	)
    OUTPUT	tgt.$IDENTITY$
    SELECT	@DealKey,
    	@DocumentName,
    	ISNULL((SELECT 1 + MAX(Rev) FROM dbo.tblDealDocuments WHERE DealKey = @DealKey AND DocumentName = @DocumentName), 0);


    N 56°04'39.26"
    E 12°55'05.63"


    • Edited by SwePesoMVP Monday, February 20, 2012 7:36 PM
    Monday, February 20, 2012 7:26 PM
  • Edit: No you cannot use a MERGE statement in this scenario since INSERT statement is required for both matching and unmatching cases. The best option is use the traditional way Vanay or Arun has shown inside a transaction block

    http://technet.microsoft.com/en-us/library/bb510625.aspx

    - Krishnakumar S

    Monday, February 20, 2012 7:37 PM