Solved

MS SQL 2014 get record ID after record is inserted.

Posted on 2016-11-04
12
78 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 40

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 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