June 20, 2012 at 11:49 am
I have a question on how to pass a parameter equalling NULL to a stored procedure linked to my SSRS 2008 R2 report. The proc is used for a scheduled report version which will have a value in the parameter @rpt_schedule. The ad-hoc version of the report allows the users to put in start and end dates and therefore I programmed the script to handle a null flag for @rpt_schedule.
In the ad-hoc report, I have created a hidden parameter and set the default to <null> . When I execute the report I get an error telling me that the script expects parameter @rpt_schedule, which was not supplied. My hidden parameter is obviously not passing a null. Any ideas on how to resolve are appreciated.
June 20, 2012 at 7:28 pm
I think if you add = null to the parameter declaration in the stored procedure as such:
alter procedure dbo.ProcedureBlah
@BeginDate date = null
as
...
it will act as a default value for your procedure param if none is supplied
June 21, 2012 at 8:20 am
I am using the script for a scheduled report where I will pass in values like D below so I don't want to default it to null. Other than the code right below I have no reference to the field except the opening Alter Proc...variable.
if @scheduled_rpt_period is not null
begin
if upper(@scheduled_rpt_period) = 'D'
begin
Shouldn't this be enough to accept the null?
August 11, 2018 at 5:14 pm
I know this is a golden oldie, but from https://stackoverflow.com/questions/12515071/ssrs-returning-different-results-than-the-stored-procedure/51804387#51804387
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.)
See also How to use stored proc with null parameters in SSRS?
August 14, 2018 at 11:53 am
If your report were not running when you failed to supply a value for a given parameter, then you would need to be sure that the parameter properties ALLOW a NULL value to be specified, even if the parameter is hidden. As one poster suggested, providing a default value of NULL in the stored procedure can be more flexible. One can also use "blank" values (aka empty strings) and in the stored procedure, use SET @ParamName = NULLIF(@ParamName, ''); as a means to NULL it out if blank, and of course, one would then have to "allow blanks" for that parameter. I suspect that folks figured that out 6 years ago, but just moved on from the thread without posting back the eventual solution.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 14, 2018 at 11:55 am
Also, if your procedure was not actually receiving that NULL parameter value, it could ONLY be because you didn't set up the dataset to use the parameter, hidden or otherwise.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 15, 2018 at 11:41 am
My stored procedure was not receiving a null value from SSRS. NULL was specified on the sp parameter definition. Allow Nulls was specified for the sp parameter in SSRS. I put a trap to check for the param not being null (essentially If @param is not null begin raiserror ... Return end)The trap kept firing until I changed SSRS as I described. Roy
August 16, 2018 at 7:01 am
Roy Latham - Wednesday, August 15, 2018 11:41 AMMy stored procedure was not receiving a null value from SSRS. NULL was specified on the sp parameter definition. Allow Nulls was specified for the sp parameter in SARS. I put a trap to check for the param not being null (essentially If @param is not null begin raiserror ... Return end)The trap kept firing until I changed SSRS as I described. Roy
Oh, okay... I think I know what was happening. The code in SSRS often needs to have the word Nothing used in order to represent a NULL value. There are some cases where Null is acceptable, but often that requires a pair of parentheses. It might have just been the way that NULL was being specified.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply