Getting error when i run the sp in where condition

[NareshN]

Hi,


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

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 [B]where CampaignID=@CampaignId[/B])
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

[mzmishra]

i think u are not creating the @query properly.may be missing some single quote.print the @query and see what you are getting before u execute that.