Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL 2014 get record ID after record is inserted.

Posted on 2016-11-04
12
Medium Priority
?
98 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 1400 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 600 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 35

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 35

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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…
In this article I will describe the Detach & Attach 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.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline

879 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