SQL Server Reporting Services [SSRS] – Error – Null is not declared. ‘Null’ constants is no longer supported; use ‘System.DBNull’ instead

Background

It is a little bit of work to use null-able arguments in Sql Server Reporting Services (SSRS).

 

In DataSet Properties, Tried using “=(Null)”

For the dataset, when we set the parameter value to =Null

DatasetProperties-Parameters-(Null)

 

 Textual:

Error - Null is not declared.  'Null' constants is no longer supported; use 'System.DBNull' instead
Image:

NullConstantIsNoLongerSupported

 

In Dataset Properties, tried using “System.DBNull”

In Dataset properties, When we set the parameter value to =System.DBNull

DatasetProperties-Parameters-System.DBNull

Text
Error: [BC30691] 'DBNull' is a type in 'System' and cannot be used as an expression.

Image:
DBNullIsATypeInSystemAndCannotBeUsedInAnExpression

In Dataset Properties, tried using “=Nothing”

Tried setting the parameter value to:

  • =Nothing
  • =System.Convert.DBNull

 

DatasetProperties-Parameters-Nothing

 

We get an error.

Error

In Designer, when trying to preview the report.

Textual:

One or more parameters required to run the report have not been specified

 

Image:

OneOrMoreParametersRequiredToRunTheReportHaveNotBeenSpecified


 

Report Parameter – Default Value – Setting Parameter Value

  • Access the parameter
  • Access the “Default Values” panel
  • In the “Default Values” panel
    • In “Select from one of the following options” radio group, choose the “Specify values” option
    • In “Value” set “(Null)

ReportParameter-DefaultValues-SpecifyValues-Value-(Null)

 

Does not help

Dataset Properties – Report Parameter

Let us remove the parameter

Original:

Here are the dual parameters.

DatasetProperties-Parameter-Null-Included

Revised:

We have removed the second parameter.

DatasetProperties-Parameter-Null-Excluded

Error Message:

OneOrMoreParametersRequiredToRunTheReportHaveNotBeenSpecified (2nd Parameter removed)

Report Parameter – Set Value to Empty

Let us go set the parameter’s value to “” or System.String.Empty.

DatasetProperties-Parameter-SetToEmptyString

Things are good.

SQL Server Profiler

Here is what we see in SQL Server Profiler:

SQLServerProfiler-ParameterValueToEmpty

Transact SQL

In Transact SQL, make sure of the following:

    • The arguments is nullable — (variable [datatype] = null )
    • That you have same code line for when the variable is null and empty

 Sample Code:


if object_id('[dbo].[usp_city]') is null
begin

    exec('create procedure [dbo].[usp_city] as select 1/0 as [shell] ')

end
go

alter procedure [dbo].[usp_city]
(
      -- make sure @countryCode is nullable
      @countryCode varchar(255) = null

     --make sure that @country is nullable
    , @country     varchar(255) = null
)
as

    select
              vc.country
            , vc.countryCode
            , vc.city

    from   [dbo].[vw_city] vc

    where  (

                (
                    vc.countryCode
                                = case
                                        when (@countryCode is null) then vc.countryCode
                                        when (@countryCode = '') then vc.countryCode
                                        else @countryCode
                                  end
                )

                and
                (
                    vc.country
                                = case
                                        when (@country is null) then vc.country
                                        when (@country = '') then vc.country
                                        else @country
                                  end
                )

           ) 

    order by
                  vc.country asc
                , vc.city

go

 

Conclusion

It appears that SSRS does not really support the setting of parameters to Null, but it supports empty string.

 

References

SSRS

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s