CODEWITHSUNDEEP
sqlsql-serverstored-procedures
MrM
MrM

Reputation: 21989

How do I return the IDENTITY for an inserted record from a stored Proecedure?

I am adding data to my database, but would like to retrieve the UnitID that is Auto generated. 

using (SqlConnection connect = new SqlConnection(connections)) 
{ 
SqlCommand command = new SqlCommand("ContactInfo_Add", connect); 
command.Parameters.Add(new SqlParameter("name", name)); 
command.Parameters.Add(new SqlParameter("address", address)); 
command.Parameters.Add(new SqlParameter("Product", name)); 
command.Parameters.Add(new SqlParameter("Quantity", address)); 
command.Parameters.Add(new SqlParameter("DueDate", city)); 
connect.Open(); 
command.ExecuteNonQuery(); 
}

...

ALTER PROCEDURE [dbo].[Contact_Add] 
@name varchar(40), 
@address varchar(60), 
@Product varchar(40), 
@Quantity varchar(5), 
@DueDate datetime 
AS  
BEGIN 
 SET NOCOUNT ON; 

 INSERT INTO DBO.PERSON 
 (Name, Address) VALUES (@name, @address) 
 INSERT INTO DBO.PRODUCT_DATA 
 (PersonID, Product, Quantity, DueDate) VALUES (@Product, @Quantity, @DueDate) 
END

Upvotes: 2

Views: 239

Answers (4)

Kronass
Kronass

Reputation: 5406

in the Stored procedure Add in the end

SELECT SCOPE_IDENTITY();

in the C# define an integer outside using and assign it inside using

int UnitID;

UnitID = command.ExecuteScalar();

Upvotes: 2

HLGEM
HLGEM

Reputation: 96552

Search Books Online for OUTPUT clause if you have SQL server 2008. This is the best method.

Upvotes: 0

Ray
Ray

Reputation: 21905

Add an output parameter to your procedure:

@new_id int output

and after the insert statement, assign it value:

set @new_id = scope_identity()

The add it to your calling code:

command.Parameters.Add("@new_id", SqlDbType.Int).Direction = ParameterDirection.Output;

and retrieve the value:

int newId = Convert.ToInt32(command.Parameters["@new_id"].Value);

Upvotes: 7

Alex K.
Alex K.

Reputation: 175748

With MSSQL its safer to use:

SET @AN_INT_OUTPUT_PARAM = SCOPE_IDENTITY()

Or simply 

RETURN SCOPE_IDENTITY()

Upvotes: 3

Related Questions