locked
Null or Blank Parameter Not being passed to Stored Procedure? RRS feed

  • Question

  • I have a report that takes to parameters:

     

    UserNumber

    CurrentYear

     

    User Number will always be passed to this report, but CurrentYear should only be passed if wanting to look at historical data for a specified year.  If nothing is passed for CurrentYear then the Stored Procedure that this report calls on will determine the CurrentYear by itself (by querying data to see what is the current year (this is not the Current Year we're in right now, but the latest year we have data for)).

     

    I can't seem to get the report to correctly talk to the Stored Procedure though.  Through MS SQL Server Manager I am able to call on this Stored Procedure with no reference to CurrentYear and it produces the data im looking for with the Current Year's data.  If I pass CurrentYear as, say 2007 it gives me that data as well.

    When trying to use the Report I've written I can pass a year to it and it gives me the correct data, but if I leave it blank or NULL I get no results back.

    I'm trying to figure out where my disconnect is in thinking this through. Am I using the wrong syntax?  Am I going about this wrong?  I would figure that if my thought process was wrong that I wouldnt be able to call directly to the Stored Procedure in MS SQL SM and get data back, so I'm assuming my Stored Procedure is correct.

     

    I've tried having CurrentYear set to accept NULL as well as Blank, AND having them set for both.

    My Stored Procedure had checks to see if CurrentYear was NULL or '' and it only works if its called directly in MS SQL SM and fails when being called by a report.

     

    Thanks for your time.

    Monday, June 21, 2010 9:29 PM

Answers

  • After I hit submit on this I noticed that I could set the CurrentYear Parameter to a SQL statement.  I did this with the same thought process that was in my Stored Procedure and it fixed my issue.
    Monday, June 21, 2010 10:16 PM