SQL Server 2000 Reporting Services Parameter Gotcha

Here is a situation I recently ran across. We created a report which executed a stored procedure. The parameters being passed had default values, so that when they were not qualified, they would default to pre-assigned values. Thus, when these "optional" parameters showed in the Data Set tab, and we executed the procedure after setting them to NULL, we encountered the error below.

Reporting Services uses ADO.NET to retrieve data from data sources. This is a straight ADO.NET error. The problem here is that the specification of NULL for a parameter, does not exclude the parameter, but causes the Stored Procedure to ignore the default. This NULL specification just happened to blow up our stored procedure. The stored procedure did not raise an error, but the result set was empty. Thus, when we changed the stored procedure to explicity check for a NULL, everything worked.