CODEWITHSUNDEEP
sqlsql-servert-sql
user3128053
user3128053

Reputation:

Return AutoIncremeting ID upon insert

I have a stored procedure that inserts into a table that has two values, id and username. The id field is autoincremeting so my stored procedure looks like:

CREATE PROCEDURE [dbo].[sp_test]
    @username varchar(50)
AS 
BEGIN
    INSERT INTO dbo.testtable(username)
        SELECT
            @username
        FROM 
            tbl.test2
        WHERE 
            username IS NOT NULL

How can I return the id even when there it is not explicitly stated? I attempted the SCOPE_IDENTITY(); keyword but I was receiving blanks and nulls.

Upvotes: 1

Views: 55

Answers (2)

James Z
James Z

Reputation: 12317

If you're really inserting several rows from a table, you can get the ids this way:

INSERT INTO dbo.testtable(username)
output inserted.id
SELECT username
FROM dbo.test2
where username is not null

Upvotes: 3

Sean Lange
Sean Lange

Reputation: 33581

Taking a guess as to what you want I think it would be something more like this.

CREATE PROCEDURE [dbo].[Insert_test]
(
    @username varchar(50)
) AS 

BEGIN
    INSERT INTO dbo.testtable
    (
        username
    )
    VALUES
    (
        @username
    )

    select SCOPE_IDENTITY()
END

Upvotes: 3

Related Questions