passing Null to an SP

Question

User-963208184 posted

Hi All,

I have a report that uses an underlying SP the Sp parameters are declared:

@StartDate smalldatetime,    
 @EndDate smalldatetime,    
 @ClientID bapsID,     
 @UnitID bapsID=NULL,    
 @GradeID char(4)=NULL,    
 @SkillID char(4)=NULL,  
 @ReasonID char(4)=NULL   

In the report designer itself If we take UnitID as an example. I have set allow blank and allow null to true and also in the parameter for UnitID I have set the default to be null. When I run the report. UnitId is represented with a dropdown, If i dont select anything I receive an error stating that I must select something for UnitId. It does this for all of them.

Answer 1

User477186420 posted

Hi,

Which report you are using ex SDLC, Cristal report or etc.? and share your code to get more idea

let me know if any query

Answer 2

User-963208184 posted

It is an SSRS Report built in Business Inteligent Studio 2008.

It is a Parameter field that maps to a value on the main SP as mentioned. On the Report Parameter Properties tab, on the General section, I have ticked allow null value and allow blank value.  This column get's populated from another sp that causes this to show as a drop down on the report parameters section when you attempt to run the report. The first option int the dropdown is <select value>. All looks fine to this point. THen when you press the button to run the report, that's when I get the error that something must be selected ?????

Answer 3

User-917364509 posted

The SP that populates the dropdownlist must have a NULL value meaning the select statement for this would look something like:

Select UnitId FROM Units
Union
Select 'NULL' FROM Units

SSRS does not provide a NULL value even if you select the "Allow NULL value" option.