By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

How to handle null values in stored procedure with pivot

P: 45
Hi,

I am using this stored procedure with pivot.If i dont have data i am getting null with this stored procedure.Can u tell me how to handle null.below query is pivot.

I am using like this but getting erro
max(isnull(Head,0))
Expand|Select|Wrap|Line Numbers
  1. PIVOT
  2. (
  3. max(Head)
  4. FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
  5. ) AS PivotTable' 
  6.  
  7.  
  8. ALTER PROCEDURE [dbo].[SAR_Sp_GetForecastedHC](@date1 DATETIME, @date2 DATETIME, @date3 DATETIME, @date4 DATETIME,@CampaignId int)
  9. AS
  10. DECLARE @query VARCHAR(MAX)
  11. BEGIN
  12. 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) + ']' +
  13. 'FROM
  14. (SELECT HeadCount as Head,
  15. StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
  16. AS SourceTable
  17. PIVOT
  18. (
  19. max(Head)
  20. FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
  21. ) AS PivotTable' 
  22. EXEC(@query)
  23. END
Oct 30 '10 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Please try this query..

Enjoy Knowledge FREEDOM

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[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, isNull([' + CONVERT(VARCHAR,@date1,101) + '],0), isNull([' + CONVERT(VARCHAR,@date2,101)+ '],0), isNull([' + CONVERT(VARCHAR,@date3,101) + '],0), isNull([' + CONVERT(VARCHAR,@date4,101) + '],0)' + 
  6. 'FROM 
  7. (SELECT HeadCount as Head, 
  8. StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ') 
  9. AS SourceTable 
  10. PIVOT 
  11. max(Head) 
  12. FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ') 
  13. ) AS PivotTable'  
  14. EXEC(@query) 
  15. END
Nov 5 '10 #2

Post your reply

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