SQL - How to use a value from INSERTed table

Question

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:

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.

Answer 1

Add a GO statement just before -- MUCH LATER IN THE SQL CODE:

This will send the above statements to the server.

Answer 2

I think you need to update your WHERE clause in the -- MUCH LATER IN THE SQL CODE section to select from your temporary table:

WHERE LocationId = @newLocationId.id

Should be:

WHERE LocationId = (select top 1 id from @newLocationId)

Or if there might be multiple values in your table variable:

WHERE LocationId in (select id from @newLocationId)