@@identity for update. 1

[MarkZK]

Hi,

I'm just creating a stored procedure (in note pad - I still don't know SQL very well) and I was wondering if the @@identity will still return the RowID (primary key) during an update, or is another select needed ?.

Here is what I have in notepad so far (not tested at all - I don't have SQL locally to test it or the connection info to my host here with me). There are two tables (Users,UsersExtended).

CREATE PROCEDURE usp_AddOrUpdate
@uFirstName varchar(30),
@uLastName varchar(30),
@uIP varchar(15),
@uDate datetime,
@uReferral varchar(500),
@uUserAgent varchar(500),
@JavascriptBoolean varchar(1)

AS

Declare @uniqueId int

If Exists (Select * From Users WHERE userFirstName = @uFirstName AND userLastName = @uLastName AND userIP = @uIP)
  UPDATE Users
  SET LastLogDate = @uDate --update the date
  WHERE userFirstName = @uFirstName AND userLastName = @uLastName AND userIP = @uIP
	@uniqueId = [xxxx]? -- ? somehow set the unique RowID to a variable for checking in the extended table
ELSE
	INSERT INTO Users (userFirstName, userLastName, userIP, LogDate)
		VALUES (@uFirstName, @uLastName, @uIP, @Date)
	-- "@Date" might be better as "GETDATE" ?
	-- set the current unique ID to a variable for inserting into the extended table
	@uniqueId = @@identity -- @@IDENTITY or SCOPE_IDENTITY() or IDENT_CURRENT ?

	--insert anything new to usersextended table and keep the old
If NOT Exists (Select * From UsersExtended WHERE foreignKey = @uniqueId AND userReferral = @uReferral AND userAgent = @uUserAgent AND JavascriptEnabled = @JavascriptBoolean)
	INSERT INTO UsersExtended (foreignKey, userReferral, userAgent, JavascriptEnabled)
			VALUES (@uniqueId, @uReferral, @uUserAgent, @JavascriptBoolean)

-----------------------------
##Users Table##
RowID (primary key)
userFirstName
userLastName
userIP
LastLogDate

##UsersExtended Table##
RowID (primary key)
foreignKey (FK)
userReferral
userAgent
JavascriptEnabled

execute usp_AddOrUpdate 'Bob','Smith','127.0.0.1','03/10/2011','htp://example.com','Mozilla/5.0 (iPad; U; CPU OS;)','1'

Where the line has "[xxxx]" to help you search where I mean. As I say, untested, I'm just trying to get the basic idea down.

Thanks for taking the time !.

 

[gmmastros]

There are a couple of problems with your procedure. Some are "kinda" obvious, and some are quite subtle.

First... directly related to your question... @@Identity will not get set during an update. It is only set during an insert.

Using @@Identity is problematic, and unless you really know what you are doing, you shouldn't use it. Instead, use Scope_Identity().

The other problem you have, that is quite subtle, is your IF statement. IF statements can have Begin/Else, but they are (unfortunately) optional. If you omit the begin/else, SQL will only conditionally execute one line after it.

For example:

Declare @Blah int
Set @Blah = 1

If @Blah = 1
   Select 'Blah is 1'
Else
   Select 'Blah is not 1'
   Set @Blah = 2
  
Select @Blah

What do you the value of @Blah will be at the end of this code block? Some may think it will be 1 because it is set to 1 and the IF line will print 'Blah is 1', and then the Select @Blah will return 1. Unfortunately, this is incorrect because only 1 line is associated with the IF and the Else.

Now look at this code:

Declare @Blah int
Set @Blah = 1

If @Blah = 1
   Select 'Blah is 1'
Else
   Select 'Blah is not 1'

Set @Blah = 2
Select @Blah

Now what do you think the output will be? This is exactly the same code as the previous one. The only difference is that I moved the "Set @Blah = 2" line down and to the left.

If I were writing this procedure, it would look like this:

CREATE PROCEDURE usp_AddOrUpdate
@uFirstName varchar(30),
@uLastName varchar(30),
@uIP varchar(15),
@uDate datetime,
@uReferral varchar(500),
@uUserAgent varchar(500),
@JavascriptBoolean varchar(1)

AS

Declare @uniqueId int

Select @uniqueId = RowId 
From   Users 
WHERE  userFirstName = @uFirstName 
       AND userLastName = @uLastName 
       AND userIP = @uIP

If @uniqueId Is Not NULL
  Begin
    UPDATE Users
    SET    LastLogDate = @uDate --update the date
    WHERE  RowId = @uniqueId
  End
ELSE
  Begin
    INSERT INTO Users (userFirstName, userLastName, userIP, LogDate)
        VALUES (@uFirstName, @uLastName, @uIP, @Date)
    -- "@Date" might be better as "GETDATE" ?
    -- set the current unique ID to a variable for inserting into the extended table
    
    Set @uniqueId = SCOPE_IDENTITY()
  End
    --insert anything new to usersextended table and keep the old
If NOT Exists (Select * From UsersExtended WHERE foreignKey = @uniqueId AND userReferral = @uReferral AND userAgent = @uUserAgent AND JavascriptEnabled = @JavascriptBoolean)
    INSERT INTO UsersExtended (foreignKey, userReferral, userAgent, JavascriptEnabled)
            VALUES (@uniqueId, @uReferral, @uUserAgent, @JavascriptBoolean)

Notice how I use Begin and End in the procedure. Technically, it's not needed after the IF part, but it doesn't hurt, and helps protect you in the future if you ever decide to add more code to the Begin/End block.

Also notice that I get the unique id first, and then use that for the IF (instead of an exists check). This allows you to use the unique id for the update statement instead of the 3 conditions.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MarkZK]

I'm surprised about that single line, although I always feel like I should be adding "End If", so it does make perfect sense to use the "begin"'s "end"'s, I'm convinced ! .

I can't currently test your version of the proc, but I have no doubt at all that yours is right.

Thanks for your help George.

 

[markros]

What is your SQL Server version? If it's SQL Server 2008, then it's safer to use MERGE

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

instead of INSERT/UPDATE

PluralSight Learning Library