Solved

MS SQL 2014 get record ID after record is inserted.

Posted on 2016-11-04
12
35 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 1

Expert Comment

by:neehar gollapudi
ID: 41874590
I have used DML trigger statements (inserted table)
0
 

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 1

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now