April 10, 2002 at 5:02 am
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
April 10, 2002 at 5:18 am
quote:
FYIJust 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
April 10, 2002 at 8:03 am
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
April 10, 2002 at 8:08 am
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..
Declare
@CommandString Varchar(1000),
@WhereClause Varchar(1000),
@Surpress int,
@ID Varchar(5)
Set @ID = '12345'
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)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply