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

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

Merge approach is safer for the concurrency.

---

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


My blog

Answer 6

Hi Niami,

I thought transaction would make the approach safe. No?

- Steve

---

Steve Greenbaum

Answer 7

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

Answer 8

OK, I will use the Merge approach. Thanks for the advice

---

Steve Greenbaum

Answer 9

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

Answer 10

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

Answer 11

Unfortunately, I want to do an insert in both cases. I just want to conditionally set the Rev Value

---

Steve Greenbaum

Answer 12

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