Setting Parameter Values SSRS Sql2008R2

Posted on 2011-10-13
Last Modified: 2012-08-13
I am using the stored procedure in the SQL Report(SSRS Sql2008R2).The stored procedure has following parameters.
(@StartDate As DateTIME ,
 @EndDate As DateTIME,
 @DateFilter As Varchar(MAX),
 @AccountNumber AS VARCHAR(10),
 @BranchID VARCHAR(500)  = 0,
 @Region    VARCHAR(200)  = 0,                                  @OrderBy VARCHAR(100) = 'POSTDATE DESC',
 @LogProgress INT = 1,
 @LogError INT = 2)

The procedure returns data in sql server management studio using parameters @StartDate 1/1/2011,@EndDate 10/5/2011,@DateFilter POSTDATE BETWEEN '01/01/2011' AND '10/05/2011'
 @AccountNumber 0000123456, @BranchID ('-1'), @Region 0, @OrderBy POSTDATE

In Sql Report I set the parameters DataTypes as Date/Time,Text,integer.
I am entering the same values(the values used in Sql server management studio) for StartDate,EndDate,DateFilter,AccountNumber,BranchID,Region and Order_By parameters while report is running.

It is not returing any rows.Empty report is showing up.I am assuming something wrong in giving parameter values for the following parameters.
DateFilter: POSTDATE BETWEEN '01/01/2011' AND '10/05/2011'
BranchID: -1
Region: 0
I entered the above values in Text Boxes.
Is there any other way to set the above parameters so that it returns the data.

Question by:KavyaVS
    LVL 18

    Accepted Solution

    Probably the single quotes in the DateFilter are the issue. I'd need to see how the DateFilter parameter is used in the stored procedure? If you could provide a code snippet that would help a lot.

    I have to warn you that what I think you are trying to do with that parameter is a form of SQL injection and it is very dangerous to allow a user to input SQL commands as part of your report parameter like that. If you got it working they could in theory type ";Drop table anyTable;" in there and if the security is lax and they knew some table names then you could be in trouble.


    LVL 37

    Assisted Solution

    To avoid an issue with quotes (and SQL injection as Chris pointed out!), why don't you replace that DateFilter param with two params: StartDate and EndDate, both of type Date.

    Furthermore, to avoid possible date format issues when passing data values, I'd recommend to use "yyyy-mm-dd".  That is normally recognized all the time, no matter what your client/server's date settings are.
    LVL 28

    Assisted Solution

    You can try formatting your date as:

    WHERE Convert(DATETIME, Convert( VARCHAR(20), POSTDATE, 102), 102 ) between '01/01/2011' AND '10/05/2011'

    Author Closing Comment


    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now