INSERT INTO 存储过程的输出 [英] Output from INSERT INTO Stored Procedure

问题描述

我正在编写一个存储过程，我首先在表中插入一个新行.然后，另一个查询需要此查询生成的 ID.是否可以使用 OUTPUT 访问预先生成的 ID?

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

推荐答案

你甚至可以用一条语句完成:

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 

这篇关于INSERT INTO 存储过程的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！