How to use stored proc with null parameters in SSRS?

Question

i have a report that uses a stored procedure that is using optional parameters.I have 4 optional parameter,so in the report's preview tab ,i can 4 textboxes for the parameters.But when i dont write anything in the textboxes,i get an error..What can i do to execute my proc with null parameters is SSRS ?

score 2 · Accepted Answer · answered Mar 05 '12 at 15:03

2

you can declare parameters to procedure as follows:

@Your_parameter VARCHAR(50) = NULL

and use in the where as:

where (@Your_parameter IS NULL OR columnname = @Your_parameter )

answered Mar 05 '12 at 15:03

Vikram

8,235
33
47

score 0 · Answer 2 · answered Aug 11 '18 at 23:03

Copied from SSRS returning different results than the stored procedure

In SSRS, I was trying to pass null as the value of a parameter to a stored procedure. It seemed that no matter what I tried SSRS was using 'null' which did not work with @Parameter is null inside the stored procedure.

I created a report parameter, called @Rs_null, with a default value of (null). I could then use that.

(Creating a report variable was no good since I could not pass that a stored procedure parameter.)

score 0 · Answer 3 · answered Mar 05 '12 at 15:25

Note that 'leaving the boxes blank' probably passes a blank string to the stored procedure, so even if the procedure is written correctly (as in the code posted by Vikram), you would not see an error but you may not see the expected results either.

If your procedure is written correctly, in the SSRS designer, access the parameter properties and review the settings for the 'default value'. Be sure that the default is set to 'null', not a blank string. If the parameters are lists, you can split the value and label to allow you to pass a null value when the user selects 'none' from the list. Also access the data set properties and review the parameter mapping to be sure the report parameters are being properly mapped to the query parameters.

This is very general information as you didn't post the error message you are receiving. The information provided in the error message often provides a clue as to what might be causing the issue. If you are still struggling after reviewing the procedure information provided by Vikram and the SSRS parameters information above, posting the error message will be help folks provide you with more relevant responses to get you moving again. Best of luck!

my proc is the same with Vikram,and i did what you said but didnt work.The error is : 'Please enter a value for the parameter objectType. The parameter cannot be blank' — Erdem Gundogdu, Mar 05 '12 at 15:44
As Lee Ticket notes (and as suggested in the second paragraph of my response), this seems to indicate that the issue is with the fact that the report parameter has not been set up to allow null values. Note that I'm not talking about the procedure parameters but the report parameters. — Rose, Mar 11 '12 at 20:48

score 0 · Answer 4 · answered Mar 06 '12 at 14:40

It sounds like you have created the parameters but are not actually passing them to the stored procedure. Check that they are by looking at the data tab (see screenshot):

enter image description here

It sounds like you already have, but double check allow blanks is not checked, but allow nulls is ticked under the parameter options.

How to use stored proc with null parameters in SSRS?

4 Answers4

Linked