August 4, 2009 at 12:01 am
hi,
i have a report which pull data based on a store procedure. the procedure has two parameters in which both are assigned as NULL as the default value.
i.e. create proc myproc ( i int =null, j int=null) as ...
the dataset using this proc works fine if i specify a value say, 1000 for i or j.
if i want to specify only one value and leave the other not put in, i will get an error.... "the parameter can not be blank"
if i click properties tab and parameter, I don't know how to assign NULL as the Default Value.
Could someone please help? Thanks.
August 4, 2009 at 8:40 am
You have 3 options:
1. Select the Allow Nulls checkbox on the Parameter setup page. This will put a checkbox next to the parameter that the user will have to uncheck to insert a value. I personally don't like that.
2. Let the parameter accept a blank value and then in the dataset use an expression to convert the blank to null (I believe you use the NOTHING keyword).
3. Let the parameter accept a blank value and in the stored procedure handle the blank value like a null.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2009 at 4:01 pm
thanks for your idea.
would you have an example of using NOTHING as keyword for tsql in a store proc?
assigning NOTHING in VB or C# is fine, but, how to assign NOTHING in SSRS or the store proc?
August 11, 2009 at 8:06 am
You can't use NOTHING in the stored procedure. You would use NOTHING in an expression when passing the report parameter value to the query parameter. Like:
=IIF(String.IsNullOrEmpty(Parameters!Parameter.Value), Nothing, Parameters!Parameter.Value)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy