asked on

Returning an ID from a stored procedure???

Hi,

How do I declare in and out parameters for this stored procedure. The code seems to work, but it does not return

I want it to return the last record inserted. Here is my code. How can I have the procedure return the last Id? I am using SCOPE_IDENTITY(), so I figure that will work, but does that cause the stored procedure to RETURN the Id ??

CREATE PROCEDURE Add_Profile
@userid int, @profilename varchar(100), @startdate datetime, @enddate datetime

AS

INSERT INTO Profile
(user_ID, create_date, start_date, name, end_date)

VALUES
(@userid, getdate(), @startdate, @profilename, @enddate)

SELECT SCOPE_IDENTITY() as newId

GO

ASKER CERTIFIED SOLUTION

ptjcb

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

_agx_

ptjcb already answered your question ..

But in case you're curious, the original procedure is returns the id in a resultset, not as output parameter.

--- returns the id as a resultset
SELECT SCOPE_IDENTITY() as newId

ksaul

The way you have it should give you the new id in a result set.
>>> I want it to return the last record inserted.
If that is the case then replace

SELECT SCOPE_IDENTITY() as newId

with

SELECT *
FROM Profile
WHERE YourIdentityColumn = SCOPE_IDENTITY()

If you want to return the new id as an output parameter it would look like this

CREATE PROCEDURE Add_Profile
@userid int, @profilename varchar(100), @startdate datetime, @enddate datetime, @NewID int OUTPUT

AS

INSERT INTO Profile
(user_ID, create_date, start_date, name, end_date)

VALUES
(@userid, getdate(), @startdate, @profilename, @enddate)

SET @NewID = SCOPE_IDENTITY() as newId

GO