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

CodePudding user response：

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

CodePudding user response：

Love this community! Thanks to SMor in the comments above, his/her suggestion did the trick, it was so obvious as usual:

AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @dateTime DATETIME = GETDATE()
    INSERT INTO OrderHeaders (OrderStateId,DateTime) VALUES (1,@dateTime); 
    SELECT CONVERT(int, SCOPE_IDENTITY()) Id, @dateTime [Date]
END

CodePudding user response：

You have a couple of options here.

1. You can set a variable equal to the ID and then select the date
2. You can use an output clause
3. Since your query is just inserting a GETDATE(), you could just call GETDATE() again and not worry about selecting from the row.

Number 3 seems the easiest to me, and I personally try to avoid OUTPUT clauses.