getting error in where condition in stored procedure

Question

Post reply

getting error in where condition in stored procedure

naresh0407 93367

SSC-Addicted

Points: 479
More actions
October 5, 2010 at 7:12 am

#143633

Hi,
I am getting headcount as null for if there is no value for that particular date.Instead of that i should return 0.Please see my stored procedure.
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 where CampaignID=@CampaignID)
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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

StartDate FROM SAR_HeadCount where CampaignID=' + CAST(@CampaignID AS VARCHAR) + ')

@CampaignID is out of scope otherwise.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

naresh0407 93367 SSC-Addicted Points: 479 More actions · Answer 2

Hi,

In the above stored procedure if i dont have HeadCount for particular date i am getting value as null but i should get value as 0.Can u tell me how to do that.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

naresh0407 93367 (10/5/2010)
Hi,
In the above stored procedure if i dont have HeadCount for particular date i am getting value as null but i should get value as 0.Can u tell me how to do that.

ISNULL(value as null, 0)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

naresh0407 93367 SSC-Addicted Points: 479 More actions · Answer 4

Hi Chris,

Can u tell where i can u in the above stored procedur.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

naresh0407 93367 (10/5/2010)
Hi Chris,
Can u tell where i can u in the above stored procedur.

Naresh, I'm sorry - can I have that in English please?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

naresh0407 93367 SSC-Addicted Points: 479 More actions · Answer 6

Hi,

Please tell me how to use this function ISNULL(value as null, 0) in the stored procedure.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Why don't you put your dates into your table source instead? Like this:

'FROM

(SELECT HeadCount,

StartDate FROM SAR_HeadCount where CampaignID = ' + @CampaignID +

' AND StartDate IN (' + @date1 + ', ' + @date2 + ', ' + @date3 + ', ' + @date4 + '))

AS SourceTable

Then you won't have null values for headcount.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden