I am trying to insert a new location record in my db … and then reuse the value of the newly created id in a select statement’s where clause later on. But I’m getting a syntax error.
Code:
x
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
The error is “Must declare scalar variable” and it dies on there WHERE clause in the SELECT statement.
I also tried something like this:
IF NOT EXISTS (SELECT *
FROM LocationEnvironment
WHERE LocationId = id
FROM @newLocationId
)
But that doesn’t work either. Any tips would be appreciated.
Advertisement
Answer
Add a GO
statement just before -- MUCH LATER IN THE SQL CODE:
This will send the above statements to the server.