CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
MrG-O-L
MrG-O-L

Reputation: 35

How can I select the inserted values in a stored procedure?

I am working with a stored procedure that inserts values in a table: an ID and the current Date. I know how to get the last used ID with SCOPE_IDENTITY but I also need to SELECT the Date that was just inserted. Any ideas?

ALTER PROCEDURE [dbo].[sp_InsertOrderHeader]
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO OrderHeaders (OrderStateId,DateTime) VALUES (1,GETDATE()); 
    SELECT CONVERT(int, SCOPE_IDENTITY()) Id
END

Upvotes: 2

Views: 125

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131676

You can use the OUTPUT clause for this:

INSERT INTO OrderHeaders (OrderStateId,DateTime) 
OUTPUT inserted.OrderStateId,inserted.DateTime
VALUES (1,GETDATE());

The OUTPUT clause is used to return the modified values in all the data modification statements, ie INSERT, DELETE, UPDATE, MERGE. Like triggers, the new values are accessed through the inserted pseudo-table while the deleted/overwritten values through the deleted table

Upvotes: 6

Related Questions