Must declare the scalar variable even though it is declared

Question

I have created a script to create tables dynamically based on data and insert the data into those tables. The script contains a calls to linked server query since the source and destination tables are in different database servers

I am getting the following error while executing the stored procedure

Must declare the scalar variable "@startYear".

Following is the stored procedure. Could anybody tell me what the problem is?

CREATE PROCEDURE processFinancialStatementIds 
AS
BEGIN
    set nocount on

    declare @startYear int, @startQuarter int, @sql nvarchar(max), @tableName varchar(50);
    set @startYear = 2000;
    set @startQuarter = 1;

    while(@startYear < 2018)
    begin
        set @startQuarter = 1;

        while(@startQuarter < 5)
        begin
            set @tableName = 'FinData' + cast(@startYear as varchar) + '_' + cast(@startQuarter as varchar);
            set @sql = '
            EXECUTE (''USE CoreReferenceStaging;drop table [dbo].[' + @tableName + '];'') AT [GBIPS-I-DB324D];
            EXECUTE (''USE CoreReferenceStaging;create table [dbo].[' + @tableName + '] ( calendarYear int, calendarQuarter int, companyid bigint not null, dataitemid bigint not null, dataitemvalue numeric(28,6), fiscalyear int, fiscalquarter int, periodenddate datetime, filingdate datetime, latestforfinancialperiodflag bit, latestfilingforinstanceflag bit );'') AT [GBIPS-I-DB324D]
            EXECUTE (''USE CoreReferenceStaging;            
                insert into [dbo].[' + @tableName + '] select fp.calendarYear, fp.calendarQuarter, fp.companyid, fd.dataitemid, fd.dataitemvalue, fp.fiscalyear, fp.fiscalquarter, fi.periodenddate, fi.filingdate, fi.latestforfinancialperiodflag, fi.latestfilingforinstanceflag
                    from [Xpressfeed_dev].[dbo].[ciqFinPeriod] fp
                    inner join [Xpressfeed_dev].[dbo].[ciqFinInstance] fi on fi.financialPeriodId = fp.financialPeriodId
                    inner join [Xpressfeed_dev].[dbo].[ciqFinInstanceToCollection] fc on fc.financialInstanceId = fi.financialInstanceId
                    inner join [Xpressfeed_dev].[dbo].[ciqFinCollection] c on c.financialCollectionId = fc.financialCollectionId
                    inner join [Xpressfeed_dev].[dbo].[ciqFinCollectionData] fd on fd.financialCollectionId = c.financialCollectionId
                    where YEAR(fi.periodenddate) = cast(@startYear as varchar) and  DATEPART( quarter,fi.periodenddate) = cast(@startQuarter as varchar '')  AT [GBIPS-I-DB324D]'
            --print (@sql);
            EXEC sp_executesql @sql

            set @startQuarter += 1
        end

        set @startYear += 1;
    end
end

Modified Query

BEGIN
    set nocount on

--declare @startYear int, @startQuarter int, @sql nvarchar(max), @tableName varchar(50);
--set @startYear = 2000;
--set @startQuarter = 1;

declare @startyear int = 2000
declare @startQuarter int = 1
declare @sql nvarchar(max)
declare @tableName varchar(50)

while @startYear < 2018
    begin
        set @startQuarter = 1;

        while @startQuarter < 5
        begin
            set @tableName = 'FinData' + cast (@startYear as varchar) + '_' + cast(@startQuarter as varchar);
            set @sql = '
            EXECUTE (''USE CoreReferenceStaging;drop table [dbo].[' + @tableName + '];'') AT [GBIPS-I-DB324D];
            EXECUTE (''USE CoreReferenceStaging;create table [dbo].[' + @tableName + '] ( calendarYear int, calendarQuarter int, companyid bigint not null, dataitemid bigint not null, dataitemvalue numeric(28,6), fiscalyear int, fiscalquarter int, periodenddate datetime, filingdate datetime, latestforfinancialperiodflag bit, latestfilingforinstanceflag bit );'') AT [GBIPS-I-DB324D]
            EXECUTE (''USE CoreReferenceStaging;            
                insert into [dbo].[' + @tableName + '] select fp.calendarYear, fp.calendarQuarter, fp.companyid, fd.dataitemid, fd.dataitemvalue, fp.fiscalyear, fp.fiscalquarter, fi.periodenddate, fi.filingdate, fi.latestforfinancialperiodflag, fi.latestfilingforinstanceflag
                    from [Xpressfeed_dev].[dbo].[ciqFinPeriod] fp
                    inner join [Xpressfeed_dev].[dbo].[ciqFinInstance] fi on fi.financialPeriodId = fp.financialPeriodId
                    inner join [Xpressfeed_dev].[dbo].[ciqFinInstanceToCollection] fc on fc.financialInstanceId = fi.financialInstanceId
                    inner join [Xpressfeed_dev].[dbo].[ciqFinCollection] c on c.financialCollectionId = fc.financialCollectionId
                    inner join [Xpressfeed_dev].[dbo].[ciqFinCollectionData] fd on fd.financialCollectionId = c.financialCollectionId
                    where YEAR(fi.periodenddate) = cast(@startYear as varchar) and  DATEPART( quarter,fi.periodenddate) = cast(@startQuarter as varchar '')  AT [GBIPS-I-DB324D]'
            --print (@sql);
            EXEC sp_executesql @sql

            set @startQuarter += 1
        end

        set @startYear += 1;
    end
end

it's not declared in the scope of the dynamic SQL query you are trying to execute — Lamak, Nov 06 '17 at 20:03
either the same way you are using the other variables to concatenate the query you want, or declare it inside your dynamic SQL, whatever makes more sense for you — Lamak, Nov 06 '17 at 20:04
Possible duplicate of [T-SQL: How to use parameters in dynamic SQL?](https://stackoverflow.com/questions/1036745/t-sql-how-to-use-parameters-in-dynamic-sql) — Igor, Nov 06 '17 at 20:04
but the start year is used in the while condition to ? Do I need to declare it twice — Tom, Nov 06 '17 at 20:09
i am getting the error at the following line where YEAR(fi.periodenddate) = cast(@startYear as varchar) and DATEPART( quarter,fi.periodenddate) = cast(@startQuarter as varchar '') AT [GBIPS-I-DB324D]' — Tom, Nov 06 '17 at 21:59
You have already done half of it by this type of expresson: `[dbo].[' + @tableName + '] `. You need to do the same thing with all of the other variables in your SQL. i.e. something like `YEAR(fi.periodenddate) = '' + @startYear + ''`. The string SQL that you are submitting doesn't know anything about `@startYear` — Nick.Mc, Nov 06 '17 at 22:49

Erik-Bart Rosman · Answer 1 · 2017-11-07T20:37:16.883

You don't need to declare it twice, but you have to use your variable @startYear as a variable instead of as a fixed value by taking it outside of your brackets. See a very simplified example below.

declare @startyear int = 2015
declare @startQuarter int = 1
declare @statement nvarchar(max)
while @startyear < 2017
begin
set @startQuarter=1

while @startQuarter < 5
    begin   set @statement = 'The year is '+cast(@startyear as varchar)+', the month is '+cast(@startQuarter as varchar)
            print @statement
            set @startQuarter += 1
    end
    set @startyear +=1
end

In your script, this would lead to:

BEGIN
set nocount on
declare @startyear int = 2000
declare @startQuarter int = 1
declare @sql nvarchar(max)
declare @tableName varchar(50)

while @startYear < 2018
begin
    set @startQuarter = 1;

    while @startQuarter < 5
    begin
        set @tableName = 'FinData' + cast (@startYear as varchar) + '_' + cast(@startQuarter as varchar);
        set @sql = '
        EXECUTE (''USE CoreReferenceStaging;drop table [dbo].[' + @tableName + '];'') AT [GBIPS-I-DB324D];
        EXECUTE (''USE CoreReferenceStaging;create table [dbo].[' + @tableName + '] ( calendarYear int, calendarQuarter int, companyid bigint not null, dataitemid bigint not null, dataitemvalue numeric(28,6), fiscalyear int, fiscalquarter int, periodenddate datetime, filingdate datetime, latestforfinancialperiodflag bit, latestfilingforinstanceflag bit );'') AT [GBIPS-I-DB324D]
        EXECUTE (''USE CoreReferenceStaging;            
            insert into [dbo].[' + @tableName + '] select fp.calendarYear, fp.calendarQuarter, fp.companyid, fd.dataitemid, fd.dataitemvalue, fp.fiscalyear, fp.fiscalquarter, fi.periodenddate, fi.filingdate, fi.latestforfinancialperiodflag, fi.latestfilingforinstanceflag
                from [Xpressfeed_dev].[dbo].[ciqFinPeriod] fp
                inner join [Xpressfeed_dev].[dbo].[ciqFinInstance] fi on fi.financialPeriodId = fp.financialPeriodId
                inner join [Xpressfeed_dev].[dbo].[ciqFinInstanceToCollection] fc on fc.financialInstanceId = fi.financialInstanceId
                inner join [Xpressfeed_dev].[dbo].[ciqFinCollection] c on c.financialCollectionId = fc.financialCollectionId
                inner join [Xpressfeed_dev].[dbo].[ciqFinCollectionData] fd on fd.financialCollectionId = c.financialCollectionId
                where YEAR(fi.periodenddate) = cast(' + @startYear + 'as varchar) and  DATEPART( quarter,fi.periodenddate) = cast(' + @startQuarter + ' as varchar '')  AT [GBIPS-I-DB324D]'
        --print (@sql);
        EXEC sp_executesql @sql

        set @startQuarter += 1
    end

    set @startYear += 1;
end
end

The error is at insert into select statement at line where YEAR(fi.periodenddate) = cast(@startYear as varchar) and DATEPART( quarter,fi.periodenddate) = cast(@startQuarter as varchar '') AT [GBIPS-I-DB324D]' — Tom, Nov 06 '17 at 22:34

Must declare the scalar variable even though it is declared

1 Answers1