471,047 Members | 1,217 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,047 software developers and data experts.

Getting error when i run the sp in where condition

45
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

Expand|Select|Wrap|Line Numbers
  1. alter PROCEDURE SAR_Sp_GetForecastedHC(@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
  2. AS
  3. DECLARE @query VARCHAR(MAX)
  4. BEGIN
  5. 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) + ']' +
  6. 'FROM
  7. (SELECT HeadCount,
  8. StartDate FROM SAR_HeadCount where CampaignID=@CampaignId)
  9. AS SourceTable
  10. PIVOT
  11. (
  12. max(HeadCount)
  13. FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
  14. ) AS PivotTable' 
  15. EXEC(@query)
  16. END
Oct 5 '10 #1
1 1418
mzmishra
390 Expert 256MB
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.
Oct 5 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.