Avatar of Zbiebu
Zbiebu
 asked on

Return ID to user

Hi,

How would I be able to return the value of an identity field to the user after inserting data.
Thanks
Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
selva_kongu

8/22/2022 - Mon
selva_kongu

Asign the @@Identity into variable and return it via out param.

create proc(@Ident int out )
AS
BEGIN
insert into table ....

SET @Ident = @@IDENTITY

END
Lowfatspread

don't use @@identity it doesn't return the value you desire ...

you need to use the scope_identity() function instead

for all sql server since sql 2000

declare @id int,@err int,@rows int

insert into yourtable (column list)
  select .....

select @err=@@error,@rows=@@rowcount,@id=scope_identity()

the identity value of the last row inserted by the previous insert statement in this scope will be retrieved...

this protects you from the actions of any triggers , which maybe invoked by the statement...

another method is to use the OUTOUT clause (which is a MS SQL extension)
Zbiebu

ASKER
ALTER PROCEDURE [dbo].[usp_InsertDetails]

	@Title VARCHAR(500),
	@Description VARCHAR(255),
	@Section VARCHAR(50),
	@CompiledBy VARCHAR(20),
	@Link VARCHAR(50),
	@Version INT,
	@Date SMALLDATETIME
AS

SET NOCOUNT ON;
SET @Version = 1

INSERT INTO Details
(Title, [Description], Section, CompiledBy,Link, Version,Date)
VALUES(@Title, @Description, @Section, @CompiledBy, @Link, @Version,@Date)

Open in new window


Hi Thanks for the reply.

Above is the code for the SP for insert,  where would I place the code you mention.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
selva_kongu

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question