SQL Declare Variables and SSRS Parameters

Question

I am asking this question on behalf of a co-worker, because I am unable to help her.

She is using SQL Server 2012 and Visual Studio 2012. We are limited in what we can do here, so we cant create stored procedures (not even completely sure if that would be a solution to this.)

She has a query that contains Declare variables @Start_Date and @End_Date. She wants to know how in SSRS, how to create the parameters in SSRS so that whatever the user selects as a date for a Start Date parameter and End Date parameter will be passed to the Declare variables in her query code for the dataset.

Is there a way to do this? And if so, how? Please let me know if need any more information from my side to help with a solution.

Answer 1

When you create a new report and enter a database query like:

SELECT some_fields 
FROM table_name
WHERE field >= @ParamName1 AND field = @ParamName2

The wizard adds two parameters to the report, using the same name of the query's parameters, and of same or approximate data type.

If you're using an Stored Procedure:

MyStoredProcedure @ParamName1 varchar(20)

The result it's the same, there is a parameter in the report named ParamName1

But, the report won't take care of the variables declared inside your stored procedure, only of its parameters.