sql server reporting services: Variable in WHERE clause -- sql server reporting services

You're in the

sql server reporting services

group:

Variable in WHERE clause

rcymozart NO[at]SPAM gmail.com
1/7/2005 3:45:12 PM

sql server reporting services:

Hi All,

I searched for this, but couldn't find anything, so bear with me. I'm
also not a huge SQL expert.

I need to use a variable in the WHERE clause like so:

SELECT *
FROM...
WHERE @Newsletter IN (N'Subscribe', N'Unsubscribe')

The deal being that each newsletter has it's own field and the report
in question needs to be able to pull from which ever newsletter/field
the user specifies.

This statement doesn't work in Reporting Services (or anywhere else,
for that matter). Is it even possible to have the field in the WHERE
clause be populated by a variable? Obviously, the above SELECT
statement isn't the way to go, but can it be done some other way?
Any help is appreciated.

Robert

Re: Variable in WHERE clause

Lev Semenets [MSFT]
1/8/2005 7:59:10 PM

One possible solution is a dynamic query, i.e. query concatenated from SQL
statements and report parameters as a string expression:

="SELECT * FROM ... WHERE " + Parameters!Newsletter.Value + " IN
(N'Subscribe', N'Unsubscribe')"

Check this BOL topic for more info about dynamic queries:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_data_v1_41ir.asp

--
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

Re: Variable in WHERE clause

Rob Labbe (Lowney)
1/9/2005 9:24:16 AM

As an aside,

This may not be a great idea in a production environment (feeding parameters
direct into a SQL statement) unless you're potentially leaving yourself open
to a type of attack known as a SQL injection.

You'll need to take steps to cleanse that user input, either by providing a
drop-down list of acceptable values, or moving your sql into a stored proc
where you can do some pre-processing.

--
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis

Blog: http://spaces.msn.com/members/roblabbe

[quoted text, click to view]

Re: Variable in WHERE clause

Dejan Sarka
1/10/2005 8:28:03 AM

Check
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx.

--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training

[quoted text, click to view]

Re: Variable in WHERE clause

rcymozart NO[at]SPAM gmail.com
1/10/2005 9:21:55 AM

When I try this, I get the following error when building:

The expression for the query 'Newsletters' contains an error:
[BC30648] String constants must end with a double quote.

Here is the expression for the query that I built:

="SELECT *
FROM attributes INNER JOIN
members ON attributes.id = members.id
WHERE " + Parameters!Newsletter.Value + " = N'Subscribe' OR " +
Parameters!Newsletter.Value + " = N'Unsubscribe'"

I'm not sure what that error is telling me is wrong, it looks like I'm
using double quotes in the correct locations.

Also, we're using a picklist for people to select their criteria, so
it's all pre-selected for them, for the most part.

Robert

Re: Variable in WHERE clause

rcymozart NO[at]SPAM gmail.com
1/10/2005 11:32:06 AM

Okay, I did that. I'm still getting the quote error.

I'm still a newbie at this, so I'm not sure what you mean by character
parameter.

Here's what I have in the textbox:

="SELECT *
FROM Table1 INNER JOIN
Table2 ON Table1.id = Table2.id
WHERE " & 'Parameters!Newsletter.Value' & " in (N'Subscribe',
N'Unsubscribe')"

I tried double quotes around the parameters as well, just in case. I'm
told either way that string constants must end with a double quote.

Which part of this is the string constant that isn't ending with a
double quote? Which part is the character parameter?
Thanks for the quick replies, it's appreciated!

Robert

Re: Variable in WHERE clause

Bruce L-C [MVP]
1/10/2005 12:24:43 PM

I suggest doing the following, back up and first make sure your expression
is creating a valid query. Assign the expression to a textbox on the form so
you can see the resulting query. When creating a query string you have to
have character parameters surrounded with single quotes. Note that when you
are creating a query with a query parameter you don't have to worry about
it, when you create a query using an expression you need to create a query
such that if you put in that exact string into query analyzer it would
execute.

Also, remember that this is an expression so you should use an ampersand
instead of a plus sign when concatenating strings together.

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Variable in WHERE clause

rcymozart NO[at]SPAM gmail.com
1/10/2005 2:24:12 PM

Appreciated.

As a test, I took out the parameter from the SQL statement above so
that it read:

="SELECT * FROM Table1 INNER JOIN Table2 ON Table1.id =
Table2.id WHERE field.1 in (N'Subscribe',
N'Unsubscribe')"

I got the exact same error yet again. I expanded the IN operator out
to an OR operator and returned the exact same error.

Just for fun, I took the expression used as an example in the link
above (copied below for clarity) and copied it into the textbox. I
created the new parameter and tried to preview the report. Same error
was returned.

="SELECT * FROM vProductProfitability

WHERE Year=2003 AND

MonthNumber IN (" + Parameters!Month.Value + ")"

So I'm doing something wrong, I'm just not sure what it is.

Here is the error again: The value expression for the textbox
'textbox1' contains an error: [BC30648] String constants must end
with a double quote.

Any further suggestions?

Robert

Re: Variable in WHERE clause

rcymozart NO[at]SPAM gmail.com
1/10/2005 2:44:31 PM

I deleted my dataset, just put the SQL expression in a text box. I
don't see any string errors jumping out at me or any errors.

I'm not sure what you mean by what I did wrong with the parameters part
or what you saw right away. I understand that I'm creating a string,
but I don't see anything in this expression to be concerned with as far
as a string goes.

Just to be clear, the error I'm getting is when the report preview is
building. I don't even get so far as to select criteria and view the
report.

As a test, I rekeyed in the expression by copying and pasting bits from
the first expression and previewing on each paste. God only knows why,
I think, but it works now. The exact same expression, character for
character, but it works now. Go figure.

Thanks for the help!

Robert

Re: Variable in WHERE clause

rcymozart NO[at]SPAM gmail.com
1/10/2005 2:48:56 PM

I'm betting it was that there were carriage returns in the expression
box, having read this. Enlightening. =)

Robert

Re: Variable in WHERE clause

Bruce L-C [MVP]
1/10/2005 3:01:48 PM

Backup and just assign this expression to a textbox. Have a form with your
parameters and a textbox, nothing else. Then see what you end up with. I bet
it will jump out at you that you have an incorrect string.

Looking at it I see something right away. What is going on is you are
creating a string, if you do as I say above you see what I am talking about.
You will notice that you are not getting a valid SQL statement. You might
have more than one thing wrong but one thing that is wrong is what you did
with the parameters part

="SELECT * FROM Table1 INNER JOIN Table2 ON Table1.id =
Table2.id WHERE " & Parameters!Newsletter.Value & " in (N'Subscribe',
N'Unsubscribe')"

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Variable in WHERE clause

Bruce L-C [MVP]
1/10/2005 4:27:47 PM

Continue with the textbox until you get past this problem. One side affect
of the expression box is that it does not handle hard carriage returns, let
any word wrapping in the box happen on its own, do not force anything onto a
new line. Copy and past this is:

="SELECT * FROM vProductProfitability WHERE Year=2003 AND MonthNumber IN ("
& Parameters!Month.Value & ")"

--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn