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

回答1:

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 

回答2:

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 

回答3:

Just try this after your insert statement and use this varible into second insert statement.:-

SET @BVar=SCOPE_IDENTITY() 

回答4:

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

回答5:

Just use the following variable:

@@IDENTITY

回答6:

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