Solved

MS SQL 2014 get record ID after record is inserted.

Posted on 2016-11-04
12
61 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 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