Dynamic AND!

29 Jul 2005 1:41 AM

Adam Knight

Hi all,

I am wanting to add an optional and statement to a query.
Not quite sure how to go about it.

My attempt so far:
SELECT

bsc_assmnt_area_id, name, parent_id

FROM

bsc_assmnt_areas

WHERE

parent_id = @pid

CASE

WHEN parent_id = 0 Then And mine_id = @mid

END

Would appreciate some insight on how to do this?

Cheers,

Adam

29 Jul 2005 1:50 AM

Robbe Morris [C# MVP]

Something like this:

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=469

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp

Show quote

"Adam Knight" <d**@brightidea.com.au> wrote in message
news:%23g8Px69kFHA.3436@tk2msftngp13.phx.gbl...
> Hi all,
>
> I am wanting to add an optional and statement to a query.
> Not quite sure how to go about it.
>
> My attempt so far:
> SELECT
>
> bsc_assmnt_area_id, name, parent_id
>
> FROM
>
> bsc_assmnt_areas
>
> WHERE
>
> parent_id = @pid
>
> CASE
>
> WHEN parent_id = 0 Then And mine_id = @mid
>
> END
>
> Would appreciate some insight on how to do this?
>
> Cheers,
>
> Adam
>
>

29 Jul 2005 1:58 AM

Stu

SELECT bsc_assmnt_area_id, name, parent_id
FROM bsc_assmnt_areas
WHERE parent_id = @pid
AND mine_id = (CASE WHEN parent_id = 0 THEN @mid ELSE mine_id END)

is one way of doing it.

29 Jul 2005 2:05 AM

Aaron Bertrand [SQL Server MVP]

http://www.sommarskog.se/dyn-search.html

Show quote

"Adam Knight" <d**@brightidea.com.au> wrote in message
news:%23g8Px69kFHA.3436@tk2msftngp13.phx.gbl...
> Hi all,
>
> I am wanting to add an optional and statement to a query.
> Not quite sure how to go about it.
>
> My attempt so far:
> SELECT
>
> bsc_assmnt_area_id, name, parent_id
>
> FROM
>
> bsc_assmnt_areas
>
> WHERE
>
> parent_id = @pid
>
> CASE
>
> WHEN parent_id = 0 Then And mine_id = @mid
>
> END
>
> Would appreciate some insight on how to do this?
>
> Cheers,
>
> Adam
>
>