Solved

MS SQL 2014 get record ID after record is inserted.

Posted on 2016-11-04
12
50 Views
Last Modified: 2016-11-08
I have a table structured like this:
USERID char(3)
 LoginTime datetime
 LogoutTime datetime
 RowID int (unique)

I also have a stored procedure that successfully inserts a record when it is called. My question here is how can a retrieve the value of RowID after the record is inserted. I want to use that value to update the logout time when the user exits using another stored procedure. I have attached the beginning of that stored procedure.
InsertActivityRecord.txt
0
Comment
Question by:rwheeler23
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 350 total points
ID: 41874374
You have several options for returning the value.
  • OUTPUT parameter
  • Return value
  • Resultset

BTW I strongly recommend using SCOPE_IDENTITY() instead of @@IDENTITY

Personally I like to return the entire inserted row as a result set.  Use an OUTPUT clause to save yourself an additional query.

insert into ACTIVITY (USERID,LOGINDAT)
OUTPUT INSERTED.UserID, INSERTED.LOGINDAT
values (@UserID ,GETDATE())

Open in new window

0
 

Author Comment

by:rwheeler23
ID: 41874505
What would happen if the user logged in and then out and then back in again on the same day? It would not know which record to update. Given that ROWID is a unique identifier I was hoping I could return that value as that will always be unique.
0
 
LVL 2

Expert Comment

by:neehar gollapudi
ID: 41874590
I have used DML trigger statements (inserted table)
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:rwheeler23
ID: 41874603
Yes, that makes sense but will it me give me access to the ROWID value that was created when the record was created or should a create another field where I am creating  my own unique value and using it in the insert command? The bottom line is I want something that will uniquely identify this row so I can update it when the user is on the way out.
0
 
LVL 2

Expert Comment

by:neehar gollapudi
ID: 41874612
Are you allowed to use triggers in your environment?
0
 

Author Comment

by:rwheeler23
ID: 41874645
Yes, this application is home grown. We can do anything we want.
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 150 total points
ID: 41874759
OUTPUT as described by Brian Crowe above is the best solution and to see what you are missing maybe please have a look here http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ where they are all well described:

"SELECT @@IDENTITY
 It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
 @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it."

And BTW - to audit those SQL login/logout events you could have a session table populated by your application on each user "login"/"logout"(X-disconnect)  action.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41875068
Dont go for trigger. They are difficult to manage as you not have control when they got executed.

I strongly suggests that you go with the Brains approach. Your requirement is very simple , for this trigger is not required.

CREATE PROCEDURE sp_SaveRecord 
( 
    @UserID char(3), 
    @new_id int OUTPUT 
) 
AS 

  DECLARE @rc int 

   select @rc = 0 

   BEGIN TRANSACTION SaveRecord 
         
   insert into ACTIVITY (USERID,LOGINDAT) values (@UserID ,GETDATE()) 
   
      if (@@ERROR <> 0)                 
          BEGIN 
             ROLLBACK TRANSACTION SaveRecord 
             select @rc = @@Error 
             Goto OnExit   
          END 
           
   select @new_id = SCOPE_IDENTITY() 
               
   COMMIT TRANSACTION SaveRecord 
           
  OnExit: 
  RETURN @rc 
         
GO 

Open in new window


<<no pts pls.>>
0
 

Author Comment

by:rwheeler23
ID: 41879455
This solution correctly inserts a row into the ACTIVTY table. My question now is how do I get it to return the value of ROW_ID? ROW_ID is a unique identifier and I want to capture its value so that on the way out I can use it in a stored procedure that will update the logout time.
0
 

Author Comment

by:rwheeler23
ID: 41879478
The value for which I am seeking is @USER_ID. This script works. Now I can use @USER_ID to update the logout time.

DECLARE @USER_ID INT
DECLARE @NEW_ID INT

exec sp_SaveRecord @UserID='SUP',@NEW_ID = @USER_ID OUTPUT

PRINT @NEW_ID
PRINT @USER_ID
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41879752
Glad to know that my script works for you. Do you have any more question for us?

Thank you!

Regards,
Pawan
0
 

Author Closing Comment

by:rwheeler23
ID: 41879794
Thanks for everyone's help with this.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question