SSRS 2005 The isNull function requires two arguments

When I run a report in SSRS I get a "The isnull function requires 2 arguments (s)".

I do enter more then one value.

This is my SQL:

 
Select DI.PS,PD.BatchNo,tissloc.Tissue_Location_Name, DTH.Modified_DateTime --PB.BatchNo, PB.DonorPS,  
From DIST_Tissue_History DTH
Join setup_tissue_location tissloc on DTH.location = tissloc.tissue_loc_id
Join PR_Deposit PD on DTH.TissueDepositNo = PD.TissueDepositNo
Join DS_Donor_Info DI on PD.DonorId = DI.DonorId
Where (DTH.Modified_DateTime >= @ModifiedDateStart or @ModifiedDateStart is Null)  
And (DTH.Modified_DateTime < @ModifiedDateEnd or @ModifiedDateEnd is Null)  
AND (ISNULL(@LocationIdList, 'ALL') = 'ALL' or CHARINDEX(',' + cast(tissloc.Tissue_Loc_Id as varchar(max)) + ',', ',' + @LocationIdList + ',') > 0)

In the SSRS Visual Studio I set the location parameter as an integer. I tried with a string same error.
I use a multi value promt to populate a dropdow to pass the location id to the sql. The avlue field is Tissue Loc Id the lable field is the tissue location name. This is the SQL I use:

Select Tissue_Loc_Id,Tissue_Location_Name
From Setup_Tissue_Location
Where Status = 1

In the  data tab when I try with the following values it returns data.

Set @ModifiedDateStart = '1-5-14'  
Set @ModifiedDateEnd  =  '1-15-14'
Set @LocationIdList  =  '970,971'
SanjeetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Why you don't test only if the value is null, instead of using the ISNULL function?
Like this:
Select DI.PS,PD.BatchNo,tissloc.Tissue_Location_Name, DTH.Modified_DateTime --PB.BatchNo, PB.DonorPS,  
 From DIST_Tissue_History DTH 
 Join setup_tissue_location tissloc on DTH.location = tissloc.tissue_loc_id 
 Join PR_Deposit PD on DTH.TissueDepositNo = PD.TissueDepositNo
 Join DS_Donor_Info DI on PD.DonorId = DI.DonorId 
 Where (DTH.Modified_DateTime >= @ModifiedDateStart or @ModifiedDateStart is Null)   
 And (DTH.Modified_DateTime < @ModifiedDateEnd or @ModifiedDateEnd is Null)  
 AND (@LocationIdList is Null or CHARINDEX(',' + cast(tissloc.Tissue_Loc_Id as varchar(max)) + ',', ',' + @LocationIdList + ',') > 0)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SanjeetAuthor Commented:
I changed it to:

Select DI.PS,PD.BatchNo,tissloc.Tissue_Location_Name, DTH.Modified_DateTime --PB.BatchNo, PB.DonorPS,  
From DIST_Tissue_History DTH
Join setup_tissue_location tissloc on DTH.location = tissloc.tissue_loc_id
Join PR_Deposit PD on DTH.TissueDepositNo = PD.TissueDepositNo
Join DS_Donor_Info DI on PD.DonorId = DI.DonorId
Where (DTH.Modified_DateTime >= @ModifiedDateStart or @ModifiedDateStart is Null)  
And (DTH.Modified_DateTime < @ModifiedDateEnd or @ModifiedDateEnd is Null)
AND (@LocationIdList is Null or CHARINDEX(',' + cast(tissloc.Tissue_Loc_Id as varchar(max)) + ',', ',' + @LocationIdList + ',') > 0)

Able to run in sql server and in the data tab of SSRS visual studio.

Added the parameters in parameters section of the data tab. Rebuilt the report clicked on refresh ran the report in the data tab (results returned).

When I run in preview I get an error stating, In correct syntax near ','.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The error doesn't help because there's many ',' in the code.

It will be good if you could retrieve the values for your variables. There's a way that you can debug it to try to find those values?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

James0628Commented:
Any chance that this is actually dynamic SQL?  That might explain the errors.

 James
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, but then you need to check in which conditions the dynamic SQL fails. Must be some empty value for one variable or so.
0
James0628Commented:
Not necessarily empty.  He said that @LocationIdList was '970,971'.  Going back to his original code, and depending on how he put things together

ISNULL(@LocationIdList, 'ALL')

 could become

ISNULL(970,971, 'ALL')


 That would presumably give him the error saying that ISNULL requires 2 arguments.

 James
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I see. It's the comma. For decimal numbers you need to use the dot (970.971).
0
SanjeetAuthor Commented:
I changed the parameter variable for locationLIst to a float in the Layout -> Parameter section got the same results. Same error message.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Problem it's not with the datatype but how it's passed to SQL Server. You should find a way to guaranty that a dot it's passed instead of a comma. Maybe use string datatype and control it during conversion to string?
0
SanjeetAuthor Commented:
How would I do that?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Unfortunaly I'm not expert in SSRS but should have events or something like that so you can change values before the report being processed, right?
0
DcpKingCommented:
Take the code and put it into a stored procedure, test it, and get it to work correctly.

Then use it in SSRS by referring to the stored procedure instead of the code.

Much easier, much more reliable.

hth

Mike
0
SanjeetAuthor Commented:
Ok will try
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.