Return ID

[aaronjonmartin]

Hi, not sure if this is the correct place to post this but here goes.

Im a total newbie with SQL and i have made a stored procedure which inserts data into columns in my database which is great. Now the table im inserting into has an ID column which is an Int which increments everytime a new record is added. What i would like to add to my stored procedure is a way of returning this ID for the row i have just inserted. Does anybody know if i can do this and how?

This is my stored procedure currently:

CREATE PROCEDURE SPInsStandalone 
@CandTitle nvarchar(50),
@CandFName nvarchar(50),
@CandSName nvarchar(50),
@CandAdd1 nvarchar(50),
@CandAdd2 nvarchar(50),
@CandAdd3 nvarchar(50),
@CandCounty nvarchar(50),
@CandCountry nvarchar(50),
@CandPCode nvarchar(50),
@CandTelNo nvarchar(50),
@CandMobile nvarchar(50),
@CandEmail nvarchar(50),
@CandFileName nvarchar(50)
AS 

BEGIN

INSERT INTO Candidate (CandTitle, CandFName, CandSName, CandAdd1, CandAdd2, CandAdd3, CandCounty, CandCountry, CandPCode, CandTelNo, CandMobile, CandEmail, CandFileName)
VALUES(@CandTitle, @CandFName, @CandSName, @CandAdd1, @CandAdd2, @CandAdd3, @CandCounty, @CandCountry, @CandPCode, @CandTelNo, @CandMobile, @CandEmail, @CandFileName)

RETURN
END

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[gradley]

Try this:

CREATE PROCEDURE SPInsStandalone 
@CandTitle nvarchar(50),
@CandFName nvarchar(50),
@CandSName nvarchar(50),
@CandAdd1 nvarchar(50),
@CandAdd2 nvarchar(50),
@CandAdd3 nvarchar(50),
@CandCounty nvarchar(50),
@CandCountry nvarchar(50),
@CandPCode nvarchar(50),
@CandTelNo nvarchar(50),
@CandMobile nvarchar(50),
@CandEmail nvarchar(50),
@CandFileName nvarchar(50)
AS 

Declare @intIdentity integer
BEGIN

INSERT INTO Candidate (CandTitle, CandFName, CandSName, CandAdd1, CandAdd2, CandAdd3, CandCounty, CandCountry, CandPCode, CandTelNo, CandMobile, CandEmail, CandFileName)
VALUES(@CandTitle, @CandFName, @CandSName, @CandAdd1, @CandAdd2, @CandAdd3, @CandCounty, @CandCountry, @CandPCode, @CandTelNo, @CandMobile, @CandEmail, @CandFileName)

SELECT @intIdentity = Scope_Identity()

RETURN @intIdentity
END

 

[yksvaan]

gradley must covered it so thank him, I just wanted you to know there's talk about it in FAQ

Getting back the Id number of a new record.
thread183-870259

A significant part of questions coming in I think can be found answered in faqs (or using search) on Microsoft SQL Server: Programming Forum or this forum. Also many members, like gradley, are happy to write the solution for you.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]