How to use comparison parameters SSRS Visual Studio

[Gonfer]

Hi guys,

I am working in a project report for a single table. One of the reports is in cascade, reason why I am using parameters in the query for the Dataset, If I would need only indicate some specific data for each parameter, I don't have any problem. my problem starts when I need to indicate in the query some comparison operator in the parameter like >=, <=. I have tried to insert in the sql query of the Dataset properties one @operator but it doesnt work:

SELECT DISTINCT Guest, last_name, first_name, zip, area_code, phone, Occurrences, Memberships, Accounts
FROM MYTABLE
WHERE (memberships +@operators @p_memberships) AND ISNULL(Accounts, 0) = ISNULL(@p_accounts, 0)
ORDER BY last_name, first_name

meberships is a field, @operators could be >=, <=..etc, @ memberships must be any value. Example: memberships >= 1

How can I set up this in the SSRS environment?

I realy appreciate some support

Gonfer

 

[gmmastros]

I'm not sure how you would set this up in SSRS, but I can tell you how you can get the query to work.

The problem is that you cannot 'simply' use a parameter for a comparison operator. In fact, the only way to get this to work is to use dynamic sql. This is when you build a query as a string, and then execute it dynamically. There are various reasons to avoid using dynamic sql, but in certain circumstances, this is the only way to accomplish your goal.

With dynamic sql, you need to declare a string, make that string look like a query, and then execute it. The way I usually do this is to build the query with place holders for my variables, and then use a series or replaces to swap in the actual values. Something like this:

SELECT DISTINCT Guest, last_name, first_name, 
       zip, area_code, phone, Occurrences, 
       Memberships, Accounts
FROM   MYTABLE
WHERE  memberships ~PARAM_OPERATOR~ ~PARAM_MEMBERSHIP~
       AND ISNULL(Accounts, 0) = ISNULL(@p_accounts, 0)
ORDER BY last_name, first_name

If there are any single quotes, I double them up now. You don't have any, so this step is skipped.

Next, I declare a variable and set it's value, like this:

SET NOCOUNT ON
Declare @SQL VarChar(8000)

Set @SQL = 'SELECT DISTINCT Guest, last_name, first_name, 
                   zip, area_code, phone, Occurrences, 
                   Memberships, Accounts
            FROM   MYTABLE
            WHERE  memberships ~PARAM_OPERATOR~ ~PARAM_MEMBERSHIP~
                   AND ISNULL(Accounts, 0) = ISNULL(@p_accounts, 0)
            ORDER BY last_name, first_name'

Then I swap out the values, like this:

SET NOCOUNT ON
Declare @SQL VarChar(8000)

Set @SQL = 'SELECT DISTINCT Guest, last_name, first_name, 
                   zip, area_code, phone, Occurrences, 
                   Memberships, Accounts
            FROM   MYTABLE
            WHERE  memberships ~PARAM_OPERATOR~ ~PARAM_MEMBERSHIP~
                   AND ISNULL(Accounts, 0) = ISNULL(@p_accounts, 0)
            ORDER BY last_name, first_name'

Set @SQL = Replace(@SQL, '~PARAM_OPERATOR~', @operators)
Set @SQL = Replace(@SQL, '~PARAM_MEMBERSHIP~', @p_memberships)

As the last step, I execute the sql, like this:

SET NOCOUNT ON
Declare @SQL VarChar(8000)

Set @SQL = 'SELECT DISTINCT Guest, last_name, first_name, 
                   zip, area_code, phone, Occurrences, 
                   Memberships, Accounts
            FROM   MYTABLE
            WHERE  memberships ~PARAM_OPERATOR~ ~PARAM_MEMBERSHIP~
                   AND ISNULL(Accounts, 0) = ISNULL(@p_accounts, 0)
            ORDER BY last_name, first_name'

Set @SQL = Replace(@SQL, '~PARAM_OPERATOR~', @operators)
Set @SQL = Replace(@SQL, '~PARAM_MEMBERSHIP~', @p_memberships)

Exec (@SQL) -- This is what actually runs the query against the database

Please remember that using Dynamic SQL should be considered and "almost" last resort.

Again... I'm not sure how you would use this within the context of SSRS, but I imagine you shouldn't have any problem with that either.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom