Request error: must declare scalar variable in complex MSSQL query

Question

We are trying to run the following MSSQL Query via Node JS:

--CREATE PROCEDURE SPTatTimeTable
--AS
--START: Takes Query And Creates MetricsTable Variable Table--
SET NOCOUNT ON
DECLARE @MetricTable Table
    (
    [NoteCreatedDate] DATETIME,
    [ServiceRequestNumber] BIGINT,
    [WorkOrderNumber] INT,
    [ActionEvent] VARCHAR(50)
    )
 
INSERT INTO @MetricTable
 
--START: Solidifies Query into Table for MetricsTable--
 
SELECT  MetricsTable.[NoteCreatedDate]
        ,MetricsTable.[ServiceRequestNumber]
        ,MetricsTable.[WorkOrderNumber]
        ,MetricsTable.[ActionEvent]
 
        --START: Query To Pull All Customer Notes From Last 3Months And Assign Generic Codes(ActionEvent)--
 
FROM    (   SELECT  UN.created_date AS [NoteCreatedDate]
            ,WO.po_sr AS [ServiceRequestNumber]
            ,WO.work_order_id AS [WorkOrderNumber]
            ,CASE   WHEN UN.note_description = 'Work Order Created' THEN 'Work Order Created'
                    WHEN UN.note_description = 'Generated Confirmation Email' OR un.note_description LIKE '%Generated Confirmation Email' THEN 'Replacement Confirmed'
                    WHEN UN.note_description LIKE '%Replacement Ordered%' OR un.note_description LIKE '%Replacement Ordered – ETA Date:%' THEN 'Replacement Ordered'
                    WHEN UN.note_description LIKE 'Closed Work Order: Escalated%' THEN 'Escalated'
                    Else 'Inserted Note'
            END AS  [ActionEvent]
    FROM    CRM.CRM.work_order WO
            LEFT JOIN crm.crm.user_note UN ON WO.id = UN.work_order_id
    WHERE   WO.work_order_id IN (   SELECT  work_order_id
                                    FROM    [CRM].[CRM].[work_order]
                                    WHERE   created_date >= '2019-01-01 00:00:000' AND wo_status_code = 'CLSD' AND wo_sub_status_code = 'SHP' AND rma = 0 AND dealer = 'ATT ASURION')
 
    --END: Query To Pull All Customer Notes From Last 3Months And Assign Generic Codes(Events)--
 
        ) AS [MetricsTable]
 
    --END: Solidifies Query into Table for MetricsTable--
 
--END: Takes Query And Creates MetricsTable Variable Table--
 
WHERE   MetricsTable.[ActionEvent] NOT IN ('Inserted Note')
 
DECLARE @TatTimeTable Table
    (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    ServiceRequestNumber BIGINT,
    WorkOrderNumber INT DEFAULT(-1),
    WorkOrderType VARCHAR(50),
    WorkOrderCreated DATETIME,
    ConfirmedStart DATETIME,
    OrderedStart DATETIME,
    WorkOrderStop DATETIME,
    LastEvent VARCHAR(50)
    )
 
--START: Cursor Variables--
 
DECLARE @NoteCreated DATETIME;
DECLARE @ServiceRequestNumber BIGINT;
DECLARE @WorkOrderNumber INT;
DECLARE @ActionEvent VARCHAR(50);
 
--END: Cursor Variables--
 
--START: Variables--
 
DECLARE @ID INT;
DECLARE @LastWorkOrder INT;
    SET @LastWorkOrder = -1;
DECLARE @WorkOrderStart DATETIME;
DECLARE @LastNoteDate DATETIME;
DECLARE @LastEvent VARCHAR(50);
 
--END: Variables--
 
--END: Creates TatTimeTable Variable Table--
 
--START: TatTime Cursor--
 
DECLARE TatTimeCursor Cursor 
FOR SELECT t.[NoteCreatedDate]
    ,t.[ServiceRequestNumber]
    ,t.[WorkOrderNumber]
    ,t.[ActionEvent]
FROM @MetricTable t
ORDER BY t.[WorkOrderNumber],t.[NoteCreatedDate]
 
OPEN TatTimeCursor;
    FETCH NEXT FROM TatTimeCursor
    INTO @NoteCreated, @ServiceRequestNumber, @WorkOrderNumber, @ActionEvent;
    While @@Fetch_Status = 0
    BEGIN
        IF @WorkOrderNumber != @LastWorkOrder and @LastWorkOrder != -1 --Only Runs When the WorkOrder is First Inserted Into Cursor
        BEGIN 
            UPDATE @TatTimeTable
            SET LastEvent = @LastEvent
            WHERE ID = @ID;
            UPDATE @TatTimeTable
            SET WorkOrderStop = @LastNoteDate
            WHERE WorkOrderNumber = @LastWorkOrder;
        END
 
        SET @LastWorkOrder = @WorkOrderNumber;
        SET @LastNoteDate = @NoteCreated;
        SET @LastEvent = @ActionEvent;
 
        IF @ActionEvent = 'Work Order Created'
        BEGIN 
        SET @WorkOrderStart = @NoteCreated
        INSERT INTO @TatTimeTable (ServiceRequestNumber,WorkOrderNumber,WorkOrderType,WorkOrderCreated) VALUES (@ServiceRequestNumber,@WorkOrderNumber,'WorkOrder',@NoteCreated)
        SET @ID = @@IDENTITY;
        END
 
    FETCH NEXT FROM TatTimeCursor
    INTO @NoteCreated, @ServiceRequestNumber, @WorkOrderNumber, @ActionEvent;
    END
 
    CLOSE TatTimeCursor
    DEALLOCATE TatTimeCursor
 
    UPDATE @TatTimeTable
            SET LastEvent = @LastEvent
            WHERE ID = @ID;
            UPDATE @TatTimeTable
            SET WorkOrderStop = @LastNoteDate
            WHERE WorkOrderNumber = @LastWorkOrder;
 
    --END: TatTime Cursor--
    
USE     CRM
 
SELECT   
        'Universal Pictures' AS [Client]
        ,'Talent Scouts' AS [Program]
        ,crm.getMHDWorkDays(WorkOrderCreated,WorkOrderStop) AS [Opened-Closed TAT]
        ,CASE   WHEN crm.getMHDWorkDays(WorkOrderCreated,WorkOrderStop) <= 3 THEN '0-3 Days' 
                WHEN crm.getMHDWorkDays(WorkOrderCreated,WorkOrderStop) > 3 AND crm.getMHDWorkDays(WorkOrderCreated,WorkOrderStop)  <= 5 THEN '4-5 Days' 
                WHEN crm.getMHDWorkDays(WorkOrderCreated,WorkOrderStop)  > 5 AND crm.getMHDWorkDays(WorkOrderCreated,WorkOrderStop)  <= 8 THEN '6-8 Days' 
                ELSE '+9 Days' 
            END AS [Opened-Closed TAT Group]
        ,CASE   WHEN ConfirmedStart IS NULL THEN crm.getMHDWorkDays(OrderedStart,WorkOrderStop)
                ELSE crm.getMHDWorkDays(ConfirmedStart,WorkOrderStop)
                END AS [Confirmed/Ordered-Shipped TAT] 
        ,CASE   WHEN ConfirmedStart IS NULL THEN (CASE  WHEN crm.getMHDWorkDays(OrderedStart,WorkOrderStop) <= 3 THEN '0-3 Days' 
                                                        WHEN crm.getMHDWorkDays(OrderedStart,WorkOrderStop) > 3 AND crm.getMHDWorkDays(OrderedStart,WorkOrderStop)  <= 5 THEN '4-5 Days' 
                                                        WHEN crm.getMHDWorkDays(OrderedStart,WorkOrderStop)  > 5 AND crm.getMHDWorkDays(OrderedStart,WorkOrderStop)  <= 8 THEN '6-8 Days' 
                                                        ELSE '+9 Days' 
                                                        END)
                ELSE (CASE  WHEN crm.crm.getMHDWorkDays(ConfirmedStart,WorkOrderStop) <= 3 THEN '0-3 Days' 
                                                        WHEN crm.getMHDWorkDays(ConfirmedStart,WorkOrderStop) > 3 AND crm.getMHDWorkDays(ConfirmedStart,WorkOrderStop)  <= 5 THEN '4-5 Days' 
                                                        WHEN crm.getMHDWorkDays(ConfirmedStart,WorkOrderStop)  > 5 AND crm.getMHDWorkDays(ConfirmedStart,WorkOrderStop)  <= 8 THEN '6-8 Days' 
                                                        ELSE '+9 Days' 
                                                        END)
                END AS [Confirmed/Ordered-Shipped TAT Group]
FROM    crm.work_order wo
        LEFT OUTER JOIN @TatTimeTable ON wo.work_order_id = @TatTimeTable.WorkOrderNumber

Basically, we create some temporary tables and variables, incl. @TatTimeTable, which we join to the permanent ones in the main (bottom) select query that’s used to output a table of data. However, when we try to run our script, Node.JS throws the exception:

RequestError: Must declare the scalar variable "@TatTimeTable".

Even though @TatTimeTable seems to be declared properly. The error is triggered by the last LEFT OUTER JOIN line. (We figured by commenting it out). What’s going on? We need to create that temporary table before running our main query.

Answer 1

Unlike temp tables (#), SQL server does NOT allow table variables (@) itself to be used as an alias when accessing to its columns, even in a simple SELECT statement.

Also, the error is NOT about the USE statement as it's a valid statement in a SQL batch (but you cannot use it inside a stored procedure if that's what you're planning to do -- in that case, use 3-part name as others suggested)

You can try the following example and see for yourself by enabling/disabling parts marked as UNCOMMENT & TRY. You could also read the comments in the code instead.

USE tempdb;

CREATE TABLE RealTable --- yes, you can have real tables in tempdb. they disappear after SQL server restarted
(
    [Col1] INT,
    [Col2] INT
);

INSERT INTO RealTable 
VALUES (1, 1), (1,2)

SELECT * 
INTO #TempTable  --- this time actual temp table (#)
FROM RealTable; 

DECLARE @tableVar Table --- and a table variable (@)
(
    [Col1] INT,
    [Col2] INT
);
 
INSERT INTO @tableVar
SELECT * 
FROM RealTable;

-- USE master;    ---- UNCOMMENT & TRY:  switching to another database does not cause this error. try it with enabling this

SELECT * 
FROM 
    tempdb..RealTable t
    INNER JOIN #TempTable    ON #TempTable.Col1 = t.Col1    ---- temp tables (#) works without an alias
    INNER JOIN @tableVar tv  ON tv.Col1  = t.Col1  ---- table variables (@) works ONLY with an alias
    -- INNER JOIN @tableVar     ON @tableVar.Col1  = t.Col1  ---- UNCOMMENT & TRY: without an alias, table variables (@) will give error:  Must declare the scalar variable "@tableVar".
                                                                    --- The intellisense in SSMS does NOT work in this case and also, the linter shows red squiggly lines on @tableVar, showing the same error

-- SELECT * FROM  @tableVar WHERE  @tableVar.Col1 = 1  ---- UNCOMMENT & TRY: Even this simple SELECT statement gives the same error. So it's not about the JOIN. It's about using table variable itself as the alias
                                                            --- The same result with the intellisense and the linter as above


/*
---- ENABLE THIS SECTION TO RETRY or CLEAN-UP
---- ONLY if you're sure no such tables already exist in your tempdb for another reason (better safe than sorry)

---- drop tables if exist
IF OBJECT_ID('tempdb..RealTable', 'U') IS NOT NULL
    DROP TABLE tempdb..RealTable

IF OBJECT_ID('tempdb..#TempTable', 'U') IS NOT NULL
    DROP TABLE #TempTable
*/