September 19, 2008 at 9:16 am
trying to add and "IF at the end of a select query, can you help with the syntax
example
SELECT id1, id2, id3 FROM table
IF x = true
where id5 = 7
object is to select id1,id2,& id3 from a table, if x is ture then only select based on if id5=7 else select everything in that table.
TIA.
September 19, 2008 at 9:23 am
John N (9/19/2008)
trying to add and "IF at the end of a select query, can you help with the syntaxexample
SELECT id1, id2, id3 FROM table
IF x = true
where id5 = 7
object is to select id1,id2,& id3 from a table, if x is ture then only select based on if id5=7 else select everything in that table.
TIA.
What is x? I don't see it defined any where. Is it a variable, in which case it should be @x.
😎
September 19, 2008 at 9:25 am
Try this
SELECT id1, id2, id3
FROM table
where
(id5 = 7 AND x = 1)
OR
x = 0
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 19, 2008 at 9:32 am
John
It's a bit of a guess, as Lynn has pointed out, but I reckon this is what you're looking for:
DECLARE @x VARCHAR (5)
SET @x = 'true'
SELECT id1, id2, id3
FROM table
WHERE @x = 'true' -- if this evaluates to true, then all rows will be returned
OR id5 = 7 -- otherwise, only those where [id5] = 7
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 19, 2008 at 9:32 am
Lynn Pettis (9/19/2008)
John N (9/19/2008)
trying to add and "IF at the end of a select query, can you help with the syntaxexample
SELECT id1, id2, id3 FROM table
IF x = true
where id5 = 7
object is to select id1,id2,& id3 from a table, if x is ture then only select based on if id5=7 else select everything in that table.
TIA.
What is x? I don't see it defined any where. Is it a variable, in which case it should be @x.
😎
sorry
declare @x bit
set @x = true
select id1,id2,id3 FROM TABLE
IF x = 1
then add the additional "WHERE id5 = 7" to the end of the select query.
so basically IF @x = true
I want this select statement "SELECT id1,id2,id3 FROM table WHERE id5=7"
IF@x = false
I want thise "select id1,id2,id3 from table"
Thanks
September 19, 2008 at 9:36 am
It may not be the bet way to do this, but the following may help: (Set variable @a to 1 and you will get 1 row, leave it at null and all three rows will be returned
declare @table table (id int, abc varchar(12))
insert into @table
values (1,'Hello')
insert into @table
values (2,'Hello1')
insert into @table
values (3,'Hello3')
declare @a int
--set @a = 1
select * from @table
where id = isnull(@a,id)
September 19, 2008 at 9:50 am
HI All,
as per my orignal post here is the solution with some sample data.
[font="Courier New"]
DECLARE @table TABLE
(
[id1] INT ,
[id2] INT ,
[id3] INT ,
[id4] INT ,
[id5] INT
)
INSERT INTO @table
SELECT TOP 100
ROW_NUMBER() OVER (ORDER BY GETDATE()),
ROW_NUMBER() OVER (ORDER BY GETDATE()),
ROW_NUMBER() OVER (ORDER BY GETDATE()),
ROW_NUMBER() OVER (ORDER BY GETDATE()),
ROW_NUMBER() OVER (ORDER BY GETDATE())
FROM syscomments a ,syscomments b
DECLARE @x BIT
SET @x = 0
SELECT id1, id2, id3
FROM @table
WHERE
(id5 = 7 AND @x = 1)
OR
@x = 0[/font]
The and x=1 can be removed if you want, I have put it in so you can read the code.
Chris you need to change your true to false in your code at the moment you returning the opposite unless I am misunderstanding the op
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 19, 2008 at 10:07 am
^^^^ thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply