How to handle null values in stored procedure with pivot

[NareshN]

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

PIVOT
(
max(Head)
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable' 


ALTER PROCEDURE [dbo].[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 as Head,
StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')
AS SourceTable
PIVOT
(
max(Head)
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ')
) AS PivotTable' 
EXEC(@query)
END

[Sandeep M]

Please try this query..

Enjoy Knowledge FREEDOM

ALTER PROCEDURE [dbo].[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, isNull([' + CONVERT(VARCHAR,@date1,101) + '],0), isNull([' + CONVERT(VARCHAR,@date2,101)+ '],0), isNull([' + CONVERT(VARCHAR,@date3,101) + '],0), isNull([' + CONVERT(VARCHAR,@date4,101) + '],0)' + 
'FROM 
(SELECT HeadCount as Head, 
StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ') 
AS SourceTable 
PIVOT 
( 
max(Head) 
FOR StartDate IN (' + '[' + CONVERT(VARCHAR,@date1,101) + '], [' + CONVERT(VARCHAR,@date2,101)+ '], [' + CONVERT(VARCHAR,@date3,101) + '], [' + CONVERT(VARCHAR,@date4,101) + ']' + ') 
) AS PivotTable'  
EXEC(@query) 
END