Reputation: 1481

Output from INSERT INTO Stored Procedure

I'm writing a stored procedure where I first insert a new row in a table. The ID that is generated by this query is then needed for another query. Is it possible to access the prevoisly generated ID with the use of OUTPUT?

This is what I have done so far and it is pretty much a guess that didnt work

ALTER PROCEDURE [dbo].[addApp]

      @Name varchar(50) 
    , @logoUrl varchar(150)
    , @siteUrl varchar(150)
    , @userId int
    , @canvasWidth int
    , @canvasHeight int

AS
DECLARE @tempId INT
SET @tempid = INSERT INTO AppInfo (name, logoUrl, userId) 
              OUTPUT inserted.id 
              VALUES(@Name, @logoUrl, @userId);
INSERT INTO CanvasApps (id, siteUrl, canvasWidth, canvasHeight)
OUTPUT inserted.id
VALUES(@tempid, @siteUrl, @logoUrl, @userId);

Upvotes: 4

Answers (6)

Devart

Reputation: 121912

Try this one -

ALTER PROCEDURE [dbo].[addApp] 

      @Name VARCHAR(50)
    , @logoUrl VARCHAR(150)
    , @siteUrl VARCHAR(150)
    , @userId INT
    , @canvasWidth INT
    , @canvasHeight INT

AS BEGIN

    DECLARE @tempId INT

    INSERT INTO dbo.AppInfo (name, logoUrl, userId) 
    SELECT @Name, @logoUrl, @userId

    SELECT @tempId = SCOPE_IDENTITY()

    INSERT INTO dbo.CanvasApps (id, siteUrl, canvasWidth, canvasHeight) 
    SELECT @tempId, @siteUrl, @logoUrl, @userId

END

Upvotes: 4

muhmud

Reputation: 4604

You need to put the results of output into a table rather than just a scalar variable:

declare @tempId table (
    id int
)

INSERT INTO AppInfo (name, logoUrl, userId) 
       OUTPUT inserted.id into @tempId 
              VALUES(@Name, @logoUrl, @userId);

Upvotes: 1

anon

Reputation:

ALTER PROCEDURE [dbo].[addApp] 
  @Name varchar(50),  
  @logoUrl varchar(150), 
  @siteUrl varchar(150), 
  @userId int, 
  @canvasWidth int, 
  @canvasHeight int
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.AppInfo (name, logoUrl, userId) 
    OUTPUT inserted.id, @siteUrl, @canvasWidth, @canvasHeight
      INTO dbo.CanvasApps(id, siteUrl, canvasWidth, canvasHeight)
    SELECT @Name, @logonUrl, @userId;
END
GO

Upvotes: 0

Nenad Zivkovic

Reputation: 18559

You can even do it in single statement:

ALTER PROCEDURE [dbo].[addApp] 

      @Name VARCHAR(50)
    , @logoUrl VARCHAR(150)
    , @siteUrl VARCHAR(150)
    , @userId INT
    , @canvasWidth INT
    , @canvasHeight INT

AS BEGIN

    INSERT INTO dbo.AppInfo (name, logoUrl, userId) 
    OUTPUT Inserted.ID, @siteUrl, @canvasWidth , @canvasHeight
       INTO dbo.CanvasApps (id, siteUrl, canvasWidth, canvasHeight)
    VALUES (@Name, @logoUrl, @userId)

END

Upvotes: 5