Retrieve the Identifier of the newly added row

Howdie y'all,

I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one...

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32)
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
GO

I actually would like to get value of the UserId column for the newly added record.

Can anyone of you folks help me with this?

Cheers,

Wes

UserID being a primary key and identity column? For example modifying the proc as follows

CREATE PROCEDURE [dbo].[spAddUser]
@.UserEmail VARCHAR(255),
@.UserPassword VARCHAR(16),
@.UserName VARCHAR(32),
@.NewID int OUTPUT
AS
INSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)
VALUES (@.UserEmail, @.UserPassword, @.UserName)
SET @.NewID = SCOPE_IDENTITY()
GO

This way you can get it via output parameter after the query is executed.

|||

Thank for this fast answer! It works like a charm now!

Cheers,

Wes

|||And to tag onto Teemu's response...
You might find a function called @.@.IDENTITY that seems to return thesame kind of information. Don't be tempted to use it, however. Itreturns the last identity value added with a wider scope thanSCOPE_IDENTIY(). It's appropriate only in very rare situations, andnever in the scenario you are using.
Don
|||

Funny you mention the @.@.Identity... I first tried that. I found it on msdn but for some reason I didn't trust it so I decided to ask the question to people who know what they are doing instead of just copy paste the code.

I'll start my mcsd course soon now and will follow the path of sql-server. I think I'll start a website where people can get extensive info on how to create and enter stored procedures on sql-server to become an MVP. I've found it hard to get some good information. I see a lot of differences in approach and syntax( should I use OUTPUT or just OUT or are they the same?) and no real good tutorial on where to start and how to learn with explanation.

Thanks again you both,

Wesley