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

user_16608744 0
Viewed 57

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
stored-procedures tsql sql-server sql
1 Answers

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

user_1342040
Related