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

dunno what your deal is without having an extra query, but if you want data from a database, you have to query it.

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);
    SELECT SCOPE_IDENTITY();
END

Answer 2

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)