Dynamic Where Clause

Question

Dynamic Where Clause

russcooper

SSC Enthusiast

Points: 111
More actions
April 10, 2002 at 5:02 am

#78599

Hi, I need some flexibility in a where clause I am generating.... This is what I want to write, but it's invalid...
select PowerID, b.BookID, b.name
from t_Power dp join t_book b On dp.bookid = b.bookid
IF @SURPRESS = 1
where b.bookid = @ID
Hence I add the where clause under certain conditions..
can anyone help ?
Edited by - russcooper on 04/10/2002 05:06:26 AM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

russcooper SSC Enthusiast Points: 111 More actions · Answer 1

quote:
FYI
Just solved :
select PowerID, b.BookID, b.name
from t_Power dp join t_book b On dp.bookid = b.bookid
where @ID =
CASE @SURPRESS
WHEN 1 THEN
b.bookid
ELSE
@ID
END

terryute Mr or Mrs. 500 Points: 574 More actions · Answer 2

Just in case you need something more dynamic...

Declare

@CommandString Varchar(1000),

@WhereClause Varchar(1000),

@Surpress int

Set @Surpress = 0

if @Surpress = 1

Set @WhereClause = ''

Else

Set @WhereClause = 'Where B.BookID = @ID'

Set @CommandString =

'Select P.PowerID, B.BookID, B.Name ' +

' From t_Power P ' +

' Join t_Book B on P.BookID = B.BookID ' +

@WhereClause

Print @CommandString -- Prints the query to be executed for debug

Exec (@CommandString)

Good Luck

terryute Mr or Mrs. 500 Points: 574 More actions · Answer 3

Oops.. a little error on the last script.

The way the script was written forces @ID to be defined prior to execution. I think the following code is correct..