SQL Server Must declare the scalar variable even though it is declared

prdp8dxp  于 2023-03-07  发布在  Scala
关注(0)|答案(1)|浏览(260)

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
bihw5rsg

bihw5rsg1#

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

相关问题