Can I Assign a variable in a SQL Stored Procedure?

Question

I am doing a SQL Insert to populate my table. I have a unique generated ID in one table that I would like to use in another table for my join. Is this possible?

.NET MVC --

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();
}

SQL SERVER --

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

The code inserts fine. Just how do I pull the Auto-generated PersonID to use in PRODUCT_DATA?

Answer 1

You can use SCOPE_IDENTITY to get the last inserted identity value:

DECLARE @PersonID INT

INSERT INTO dbo.Person (Name, Address)
VALUES (@Name, @Address)

SET @PersonID = SCOPE_IDENTITY()

INSERT INTO dbo.Product_Data (PersonID, Product, Quantity, DueDate)
VALUES (@PersonID, @Product, @Quantity, @DueDate)

Answer 2

You don't need to use SCOPE_IDENTITY(), use OUTPUT and a single INSERT statement!
this does require SQL Server 2005 and up

Try this:

setup the tables

CREATE TABLE Test1 (PersonID int identity(1,1), Name varchar(40), Address varchar(60))
CREATE TABLE Test2 (PersonID int, product varchar(40),Quantity varchar(5),DueDate datetime)

create the procedure

CREATE PROCEDURE TestSP
@name varchar(40),
@address varchar(60),
@Product varchar(40),
@Quantity varchar(5),
@DueDate datetime
AS 
BEGIN
     SET NOCOUNT ON;

     INSERT INTO Test1
             (Name, Address)
             OUTPUT INSERTED.PersonID, @Product, @Quantity, @DueDate
             INTO Test2
         VALUES 
             (@name, @address)

END

test the code

exec TestSP 'name','address','product',123,'1/1/2010'
select * from Test1
select * from Test2

output

PersonID    Name                 Address
----------- -------------------- -----------------------
1           name                 address

(1 row(s) affected)

PersonID    product   Quantity DueDate
----------- --------- -------- -----------------------
1           product   123      2010-01-01 00:00:00.000

(1 row(s) affected)