sql - 从表值参数将数据插入表中
问题描述
我正在尝试创建一个存储过程,将 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
解决方案
- 而不是使用
VALUES
子查询,只需使用SELECT
. - 始终列出您要插入的列。它更清晰并且会减少错误,特别是如果您将来修改表结构,
- 您的第一个查询似乎过于复杂 - 如果它确实有效的话。
- 您的第三个查询应该引发错误,因为您有多
IN
个子查询应该导致“子查询返回多个结果”错误。 - 数据类型是depreciated
text
usevarchar(max)
。 - 通常你想要
SET NOCOUNT, XACT_ABORT ON
. - 始终
RETURN
是一个状态,因此您的调用应用程序知道它是否成功。默认情况下会返回 0,我更愿意明确表示。 - 分号终止所有语句。
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;
推荐阅读
- c - 可以取消引用比较等于有效指针的元素指针吗?
- python - 使用 mahotas 库中的 .features.haralick fiuction 时出错
- sql - 基于分区添加列 - Oracle
- dart - 如何从运行时类型(Type)转到绑定类型参数(T)?
- python - Swap 和 Sun,自定义测试用例通过,但内置运行时错误,为什么?
- python - 如何正确连接用python中的循环提取的3个数据帧?
- debugging - 在 VS 代码中调试 C++ 期间无法在外部控制台中输入输入
- authentication - context.HttpContext.Request.Headers["Authorization"] 返回空值
- camera - 一加6T的FOV?
- windows - Windows 终端/Powershell 智能感知