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) 🙂 🙂 🙂
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) 🙂 🙂 🙂
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) 🙂 🙂 🙂
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