我正在嘗試在我的資料庫中插入一個新的位置記錄......然后稍后在 select 陳述句的 where 子句中重用新創建的 id 的值。但我收到語法錯誤。
代碼：
USE Widgets;
GO
DECLARE @userPrincipalName VARCHAR(100),
@displayName VARCHAR(100),
@domainName VARCHAR(100),
@locationId INT,
@uname VARCHAR(100);
DECLARE @newLocationId TABLE (
id INT
);
-- CREATE NEW LOCATION, FLAG AS PRIMARY
IF NOT EXISTS (SELECT *
FROM Locations
WHERE LocationName = 'outer-space')
BEGIN
INSERT INTO Locations
OUTPUT Inserted.ID INTO @newLocationId
SELECT 'out-space' ,'the final frontier', 60, 1
END
-- MUCH LATER IN THE SQL CODE:
IF NOT EXISTS (SELECT *
FROM LocationEnvironment
WHERE LocationId = @newLocationId.id
)
BEGIN
INSERT INTO LocationEnvironment
SELECT 1, id
FROM @newLocationId
END
錯誤是“必須宣告標量變數”，它在 SELECT 陳述句中的 WHERE 子句中消失。
我也嘗試過這樣的事情：
IF NOT EXISTS (SELECT *
FROM LocationEnvironment
WHERE LocationId = id
FROM @newLocationId
)
但這也行不通。任何提示將不勝感激。
uj5u.com熱心網友回復：
我認為您需要在 SQL CODE 部分中更新 WHERE 子句以從臨時表中進行選擇：
WHERE LocationId = @newLocationId.id
應該：
WHERE LocationId = (select top 1 id from @newLocationId)
或者，如果您的表變數中可能有多個值：
WHERE LocationId in (select id from @newLocationId)
uj5u.com熱心網友回復：
GO在前面添加一個陳述句-- MUCH LATER IN THE SQL CODE:
這會將上述陳述句發送到服務器。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/476562.html
標籤：sql