Return Last Id - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return Last Id

Author

Topic

esambath
Yak Posting Veteran

89 Posts

Posted - 2010-01-02 : 01:56:54

Hi Expert,

We have create one table Usermanagement

CREATE TABLE Usermanagement
(
UserId bigint IDENTITY(1,1) PRIMARY KEY,
NickName varchar (20),
)

insert into Usermanagement(Nickname)values('A')
insert into Usermanagement(Nickname)values('B')
insert into Usermanagement(Nickname)values('C')
insert into Usermanagement(Nickname)values('D')

select * from Usermanagement

insert the one record means then return the last userid

for example
insert into Usermanagement(Nickname)values('E')

To execute the query means to return the Last UserId 5

kindly help this clarifiction

if you need more clarification mean i will explain

Thanks and Advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 02:00:53

make use of SCOPE_IDENTITY() function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 02:01:23

also see

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

esambath
Yak Posting Veteran

89 Posts

Posted - 2010-01-02 : 02:26:41

quote:
Originally posted by visakh16

also see

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Hi visakh16,

Thanks for your replay.

we need to create the store procedure and return last UserId/SCOPE_IDENTITY()

please find the below code

CREATE PROCEDURE SetUsermanagement
-- Add the parameters for the stored procedure here
@Nickname varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select @var = MAX(UserId) as prev_max_UserId FROM Usermanagement
-- Insert statements for procedure here
INSERT INTO Usermanagement
(
Nickname
)
VALUES
(
@Nickname
)
SELECT @var as UserId
END

We execute procedure we got the error

Server: Msg 156, Level 15, State 1, Procedure SetUsermanagement, Line 10
Incorrect syntax near the keyword 'as'.
Server: Msg 137, Level 15, State 1, Procedure SetUsermanagement, Line 20
Must declare the variable '@var'.

Thanks an advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 02:29:22

you need to use RETURN to return id value. Alternatively you can return values through output parameters. for returning ID values i prefer latter way.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 02:33:34

so it should be


CREATE PROCEDURE SetUsermanagement
-- Add the parameters for the stored procedure here
@Nickname varchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select @var = MAX(UserId) as prev_max_UserId FROM Usermanagement
-- Insert statements for procedure here
INSERT INTO Usermanagement
(
Nickname
)
VALUES
(
@Nickname
)
SET @var =SCOPE_IDENTITY()
RETURN @var
END



or


CREATE PROCEDURE SetUsermanagement
-- Add the parameters for the stored procedure here
@Nickname varchar(20),
@ReturnIDVal int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select @var = MAX(UserId) as prev_max_UserId FROM Usermanagement
-- Insert statements for procedure here
INSERT INTO Usermanagement
(
Nickname
)
VALUES
(
@Nickname
)
SET @ReturnIDVal=SCOPE_IDENTITY()

END

and use like

DECLARE @RetID int

EXEC SetUsermanagement 'tom',@RetID OUT

SELECT @RetID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources