468,525 Members | 2,181 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,525 developers. It's quick & easy.

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 1329
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

reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.