I can't use a variable in a SQL query Msg 137 Must declare the scalar variable

Question

I want to create a query that calculates some indicators within a time range defined by some variables, but I'm not sure if it is possible to use the variables the way I'm intending.

I get the following error:

Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@vDiai".

The column TPO_CFecha is of type int.

I don't know what I'm doing wrong:

DECLARE @vDiai INT;
DECLARE @vDiaf INT;
SET @vDiai = 20171030;
SET @vDiaf = 20181030;

USE DBDMS
GO

SELECT 
    CAST(100 * Baja / (HeadcountBruto / Dias) AS decimal(20, 5)) 
FROM 
    (SELECT 
         SUM(ROT_IBAJA) AS Baja,
         COUNT(DISTINCT R.TPO_KFecha) AS Dias,
         SUM(ROT_IHCOUNT) AS HeadcountBruto
     FROM 
         TH_Rotacion R
     INNER JOIN 
         TD_Tiempo T ON R.TPO_KFECHA=T.TPO_KFecha
     WHERE 
         T.TPO_CFecha >= @vDiai
         AND T.TPO_CFecha <= @vDiaf) T
go

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME = 'TD_Tiempo' AND 
     COLUMN_NAME = 'TPO_CFecha'
go

Gordon Linoff · Accepted Answer · 2018-07-11 15:21:49Z

8

The GO breaks up your script. Just move it or remove it:

USE DBDMS
GO

DECLARE @vDiai INT;
DECLARE @vDiaf INT;
SET @vDiai = 20171030;
SET @vDiaf = 20181030;

SELECT CAST(100*Baja/(HeadcountBruto/Dias) AS decimal(20,5)) 
FROM (SELECT SUM(ROT_IBAJA) AS Baja,
             COUNT(DISTINCT R.TPO_KFecha) AS Dias,
             SUM(ROT_IHCOUNT) AS HeadcountBruto
      FROM TH_Rotacion R INNER JOIN
           TD_Tiempo T
           ON R.TPO_KFECHA = T.TPO_KFecha
      WHERE T.TPO_CFecha >= @vDiai AND T.TPO_CFecha <= @vDiaf
     ) T;

answered Jul 11, 2018 at 15:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thom A Over a year ago

To be a little more specific, GO in SSMS and sqlcmd is a batch separator. Variables only exist within the batch they are declared. As GO ends the batch, it isn't available for reference in the second batch.

Collectives™ on Stack Overflow

I can't use a variable in a SQL query Msg 137 Must declare the scalar variable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related