How to get the auto-generated primary key after insertion in stored procedure?

Question

My SQL Server table:

[dbo].[Rep] ( [rep_ID] INT IDENTITY (300, 1) NOT NULL, [Restaurant_ID] INT NULL, [Fname] VARCHAR (50) NOT NULL, [Lname] VARCHAR (50) NOT NULL, CONSTRAINT [PK_Rep_ID] PRIMARY KEY CLUSTERED ([rep_ID] ASC), CONSTRAINT [FK_Restaurant_ID] FOREIGN KEY ([Restaurant_ID]) REFERENCES [dbo].[Restaurants] ([ID])

);

I want to create a stored procedure that return the auto generated key for the inserted record.

My current stored procedure:

CREATE PROCEDURE [dbo].[createRepAcc] @first varchar(50), @last varchar(50) AS INSERT INTO Rep([Fname], [Lname]) OUTPUT inserted.rep_ID VALUES (@first, @last);

It's able to insert into the table but only return 1 instead of the primary key generated.

What am I doing wrong?

Please don't post images of code (especially when those images are broken). As for the problem, I don't see anything wrong with your query. When you run that INSERT it'll pass back the value of rep_ID for the row inserted in a dataset. — Thom A
– Thom A ♦, Commented Mar 20, 2019 at 19:15
Your proc would not work because you are not inserting a value to Restaurant_ID and that column does not allow NULL. Probably in an attempt to simplify the code you posted, you have obfuscated the actual cause of the problem. — Tab Alleman
– Tab Alleman, Commented Mar 20, 2019 at 19:16
If you change Restaurant_ID for be NULLable, your SP works fine: DB<>Fiddle. Either we're missing pieces of the puzzle, or...? — Thom A
– Thom A ♦, Commented Mar 20, 2019 at 19:17
At a guess, maybe you're using EXEC @Variable = {Stored Procedure}; syntax? If so, that might explain why. An SP returns the value 0 for a success and a non-zero value to indicate failure (which this would based on the information we have). Perhaps the failure is therefore causing the return value to be 1. If you want to pass the value back of the inserted ID to a variable you should be using OUTPUT parameters. — Thom A
– Thom A ♦, Commented Mar 20, 2019 at 19:24

Gro · Accepted Answer · 2019-03-21 04:26:37Z

You can use scope_identity() to get the last generated id. For your case here is a detailed worksheet

/* creating a simple table with identity column */ CREATE TABLE dbo.a(identity_column INT IDENTITY(300,1), x CHAR(1)); /* Procedure that inserts values and returns generated output*/ CREATE PROCEDURE [dbo].[ap] @x char(1), @id int output AS INSERT INTO a(x) VALUES (@x); select @id = SCOPE_IDENTITY(); /* This is how the procedure could be called */ declare @ident int -- declare a variable to receive output execute dbo.ap @x='b', @id=@ident output -- execute or call the procedure select @ident -- select or print the genereated id

Collectives™ on Stack Overflow

How to get the auto-generated primary key after insertion in stored procedure?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related