I am getting error in following stored procedure in where condition Must declare the scalar variable "
@CampaignId
".I already declared CampaignId but still getting error.Please see where condition which is in bold letters.If i give value its working like where
CampaignID=1
Expand|Select|Wrap|Line Numbers
- alter PROCEDURE SAR_Sp_GetForecastedHC(@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
- AS
- DECLARE @query VARCHAR(MAX)
- BEGIN
- SET @query = 'SELECT '+ CHAR(39) + 'Forecasted HC as per Hiring Plan' + CHAR(39) + ' AS HeadCount, [' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' +
- 'FROM
- (SELECT HeadCount,
- StartDate FROM SAR_HeadCount where CampaignID=@CampaignId)
- AS SourceTable
- PIVOT
- (
- max(HeadCount)
- FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
- ) AS PivotTable'
- EXEC(@query)
- END