?
Solved

SSRS 2005 The isNull function requires two arguments

Posted on 2014-09-02
13
Medium Priority
?
387 Views
Last Modified: 2014-09-15
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'
0
Comment
Question by:Sanjeet
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 53

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40298751
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
 

Author Comment

by:Sanjeet
ID: 40299079
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
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40300369
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Expert Comment

by:James0628
ID: 40300636
Any chance that this is actually dynamic SQL?  That might explain the errors.

 James
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40300668
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
 
LVL 35

Expert Comment

by:James0628
ID: 40300817
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
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40300824
I see. It's the comma. For decimal numbers you need to use the dot (970.971).
0
 

Author Comment

by:Sanjeet
ID: 40301226
I changed the parameter variable for locationLIst to a float in the Layout -> Parameter section got the same results. Same error message.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40301236
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
 

Author Comment

by:Sanjeet
ID: 40301272
How would I do that?
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40301279
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 40307462
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
 

Author Comment

by:Sanjeet
ID: 40312729
Ok will try
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question