0

I have a table Partners generated by 

CREATE TABLE Partners ( 
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    name NVARCHAR(50) NOT NULL,
    email NVARCHAR(254) NOT NULL, -- 254 is optimal length according to http://stackoverflow.com/questions/1199190/what-is-the-optimal-length-for-an-email-address-in-a-database
    PRIMARY KEY (id)
);

and a attempt at a sproc for inserting a row and retrieving the id of the inserted row: 

CREATE PROCEDURE AddPartner 
     @name NVARCHAR(50), @email NVARCHAR(254)
AS
BEGIN
    SET NOCOUNT ON 
    INSERT INTO Partners (name,email)
    OUTPUT INSERTED.id INTO @new_guid
    VALUES (@name,@email)   
    SELECT @new_guid
END

Suppose I want to (1) call AddPartner with (name,email) equal ('some dude','dude192@gmail.com) and return the id (call it dude_id) so that I can later (2) use it when I insert another row: 

INSERT INTO Answers (question_id,partner_id,val) VALUES (1,dude_id,24);

How exactly can I do that? And will having GO statements between (1) and (2) ruin everything?

user5648283
  • 5,913
  • 4
  • 22
  • 32
  • 1
    If you do not have the ID at the time of import check out. http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row – Dave Kelly Feb 03 '16 at 15:46

1 Answers1

0

Add Output Parameter to your procedure

CREATE PROCEDURE Addpartner (@name     NVARCHAR(50),
                             @email    NVARCHAR(254),
                             @new_guid UNIQUEIDENTIFIER output)
AS
  BEGIN
      SET NOCOUNT ON
      INSERT INTO Partners
                  (name,email)
      OUTPUT INSERTED.id INTO @new_guid
      VALUES      (@name,@email)
  END 


DECLARE @new_guid AS UNIQUEIDENTIFIER 

EXEC Addpartner 'some dude', 'dude192@gmail.com', @new_guid  output

INSERT INTO Answers
            (question_id,partner_id,val)
VALUES      (@output_identity,'dude_id',24);
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172