Setting Parameter Values SSRS Sql2008R2

Posted on 2011-10-13
Medium Priority
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

chrismc earned 1200 total points
ID: 36966562
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

ValentinoV earned 400 total points
ID: 36966702
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 29

Assisted Solution

sammySeltzer earned 400 total points
ID: 36968257
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

ID: 36986321

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

615 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