将数据从表值参数插入表

html5 • • 问答

我正在尝试创建一个存储过程，将 TVP 传递给该过程，然后将 TVP 中的一些数据插入到两个表中。

我已经实现了存储过程，但只有第二个插入（唯一不从 TVP 读取的）在工作。另外两个不起作用（不要插入任何东西），我似乎无法弄清楚原因。

我试图在 SQL Server 中创建一个虚拟 TVP 并在那里运行该过程，但这也不起作用。这是因为 TVP 是只读的吗？我不会假设，因为我实际上并没有在 TVP 中插入或更新数据。

有没有办法使这项工作？

谢谢您的帮助！

表值参数定义：

CREATE TYPE dbo.Ingredients 
AS TABLE 
( 
  Quantity int,
  Measure nvarchar(50),
  Ingredient nvarchar(50),
)
GO

存储过程：

ALTER PROCEDURE uspCreateRecipe 
    (@IDUser int, 
     @RecipeName nvarchar(50), 
     @Category nvarchar(50), 
     @Difficulty nvarchar(50), 
     @Duration nvarchar(50), 
     @ING dbo.Ingredients READONLY, 
     @Execution text)
AS
BEGIN
    INSERT INTO dbo.Ingredients 
    VALUES ((SELECT Ingredient FROM @ING WHERE NOT EXISTS (SELECT Ingredient FROM @ING WHERE Ingredient IN (SELECT IngredientName FROM dbo.Ingredients))), 2)

    INSERT INTO dbo.Recipes 
    VALUES (@IDUser, @RecipeName, NULL, 
            (SELECT IDDifficulty FROM dbo.Difficulty WHERE Difficulty = @Difficulty), 
            (SELECT IDDuration FROM dbo.Duration  WHERE Duration = @Duration ), 
            NULL, 
            (SELECT IDCategory FROM dbo.Category WHERE CategoryName = @Category ), 
            @Execution , NULL, 2, GETDATE())

    INSERT INTO dbo.Recipes_Ingredients 
    VALUES (SCOPE_IDENTITY(), 
            (SELECT Quantity FROM @ING), 
            (SELECT IDMeasure FROM dbo.Measure WHERE Measure IN (SELECT Measure FROM @ING)), 
            (SELECT IDIngredient FROM dbo.Ingredients WHERE IngredientName IN (SELECT Ingredient FROM @ING)))
END

回答

  1. 而不是VALUES与子查询一起使用，只需使用SELECT.
  2. 始终列出您要插入的列。它更清晰，将减少错误，尤其是如果您将来修改表结构，
  3. 您的第一个查询似乎过于复杂 - 如果它确实有效的话。
  4. 您的第三个查询应该抛出错误，因为您有多IN个子查询，这些子查询应该会导致“子查询返回多个结果”错误。
  5. 该text数据类型折旧使用varchar(max)。
  6. 通常你想SET NOCOUNT, XACT_ABORT ON。
  7. 始终RETURN是一个状态，以便您的呼叫应用程序知道它是否成功。0 将默认返回，我更愿意明确表示。
  8. 分号终止所有语句。
ALTER PROCEDURE uspCreateRecipe
(
  @IDUser int
  , @RecipeName nvarchar(50)
  , @Category nvarchar(50)
  , @Difficulty nvarchar(50)
  , @Duration nvarchar(50)
  , @ING dbo.Ingredients READONLY
  , @Execution nvarchar(max) -- text is depreciated
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    INSERT INTO dbo.Ingredients ([Name], Col2)
        SELECT Ingredient, 2
        FROM @ING
        WHERE Ingredient NOT IN (SELECT IngredientName FROM dbo.Ingredients);

    INSERT INTO dbo.Recipes (IDUser, RecipeName, Col3, IDDifficulty, IDDuration, Col6, IDCategory, Col8, Col9, Col10, Co11)
        SELECT @IDUser, @RecipeName, NULL, IDDifficulty
            , (SELECT IDDuration FROM dbo.Duration WHERE Duration = @Duration)
            , NULL
            , (SELECT IDCategory FROM dbo.Category WHERE CategoryName = @Category)
            , @Execution, NULL, 2, GETDATE()
        FROM dbo.Difficulty
        WHERE Difficulty = @Difficulty;

    INSERT INTO dbo.Recipes_Ingredients (IDRecipe, Quantity, IDMeasureid, IDIngredient)
        SELECT SCOPE_IDENTITY(), Quantity
            , (SELECT IDMeasure FROM dbo.Measure WHERE Measure = I.Measure)
            , (SELECT IDIngredient FROM dbo.Ingredients WHERE IngredientName = I.Ingredient)
        FROM @ING I;

    RETURN 0;
END;

  • @Charlieface I like to be explicit about these things 🙂
以上是将数据从表值参数插入表的全部内容。
THE END
分享
二维码
< <上一篇
下一篇>>