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
1条答案
按热度按时间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.
In your script, this would lead to: