MS SQL Server MSEQ - If Statement to determine WHERE condition in SQL Query

Author

If Statement to determine WHERE condition in SQL Query

Rhonda Fischer

2005-05-12, 9:24 am

Hello,

The @parameters that I am receiving into my SQL query in SQL Serer 2000
Reporting Services are based on user selection from a drop down combo box.
If the user selects a name it will return an integer. An alternative to
selecting a single name the user can select 'ALL' of which will return a -1
or maybe '*' if allowed. I would like an if stmt to determine if a criteria
be added to the query or not. Can I do this, is there anyway of achieving the
same thing?

SELECT Forename, Username, Description
FROM Audit
WHERE Date >= @Param_StartDate
AND Date < @Param_EndDate
if @Param_UserKey <> -1
AND UserID =@Param_UserKey 'continue to add last line of SQL stmt

There are two more @Parameters to test if the 'ALL' option is selected also
not
sure I can neatly handle this. @Param_RecordType and @Param_ModuleNo.

Thank you kindly for any assistance.

Cheerio
Rhonda

Anith Sen

2005-05-12, 11:23 am

In general, you'd use a CASE statement for such requirements, for instance:

SELECT Forename, Username, Description
FROM Audit
WHERE Date >= @Param_StartDate
AND Date < @Param_EndDate
AND UserID = CASE WHEN @Param_UserKey <> -1
THEN @Param_UserKey
ELSE UserID
END ;

If the column is nullable, you will have to use COALESCE or perhaps re-write
it as:

...AND ( @Param_UserKey <> -1 AND UserID = @Param_UserKey )
OR ( @Param_UserKey <> -1 )

And there are several other approaches for such requirements, some of which
are detailed at:
http://www.sommarskog.se/dyn-search.html

--
Anith

Hugo Kornelis

2005-05-12, 8:24 pm

On Thu, 12 May 2005 07:07:39 -0700, Rhonda Fischer wrote:

>Hello,
>
>The @parameters that I am receiving into my SQL query in SQL Serer 2000
>Reporting Services are based on user selection from a drop down combo box.
>If the user selects a name it will return an integer. An alternative to
>selecting a single name the user can select 'ALL' of which will return a -1
>or maybe '*' if allowed. I would like an if stmt to determine if a criteria
>be added to the query or not. Can I do this, is there anyway of achieving the
>same thing?
>
>SELECT Forename, Username, Description
>FROM Audit
>WHERE Date >= @Param_StartDate
>AND Date < @Param_EndDate
>if @Param_UserKey <> -1
> AND UserID =@Param_UserKey 'continue to add last line of SQL stmt

Hi Rhanda,

Try this one:

SELECT Forename, Username, Description
FROM Audit
WHERE Date >= @Param_StartDate
AND Date < @Param_EndDate
AND ( @Param_UserKey = -1
OR UserID = @Param_UserKey )

>There are two more @Parameters to test if the 'ALL' option is selected also
>not
>sure I can neatly handle this. @Param_RecordType and @Param_ModuleNo.

I'm not sure what you mean. If you don't see how to adapt the above
query to handle this case, then please post more specific information.
(Check out www.aspfaq.com/5006 for the best way to post questions).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links