How to make a stored procedure return the last inserted id

Question

Here I have a stored procedure that inserts a row but how do you make it return the last inserted id without making another query

CREATE PROCEDURE [dbo].[spInsertCriteriaItem]
@GroupID int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    insert into CriteriaItem (CriteriaGroupID) VALUES(@GroupID)
    --I don't want to make another query here
END

Is it possible to do this

Answer 1

Using Sql Server you can make use of the OUTPUT clause.

Something like

DECLARE @CriteriaItem TABLE (
        ID INT IDENTITY (1,1),
        CriteriaGroupID INT
)
insert into @CriteriaItem (CriteriaGroupID)
OUTPUT INSERTED.ID
VALUES(1)