Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
borntorun75
Starting Member
4 Posts |
Posted - 2011-10-08 : 10:25:45
|
Hi,I have a stored procedure similar to the one below (very simplified ). I'm not sure whether I can do the proposed change without significantly over-inflating the procedure.select t1.field1, t1.field2 from table1 as t1where t1.field3 in (select t2.field3 from table2 as t2 where t2.field4 = 0)I'd like if possible to extend the subquery logic to conditionally the where clause to select t1.field1, t1.field2 from table1 as t1where t1.field3 in (select t2.field3 from table2 as t2if parameter1 = 'N' where t2.field4 = 0)else where t2.field4 = 0 or t2.field5 = 'X')The text in blue isn't supposed to be proper TSQL - that's just where I would logically put the test.I'm trying to avoid having to effectively duplicate the whole query but putting the IF clause right at the top of the logic. That's because in the real case scenario I have, the SELECT statement is a lot more complex.Thanks for any help or advice you might suggest. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-08 : 11:13:30
|
[code]select t1.field1, t1.field2 from table1 as t1inner join table2 as t2on t1.field3 = t2.fieldwhere ((t2.field4 = 0 and parameter1 = 'N')or (t2.field5 = 'X'))[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-09 : 07:38:27
|
i think you may use case when select t1.field1, t1.field2 from table1 as t1where t1.field3 in(case when parameter1 = 'N' then select t2.field3 from table2 as t2 where t2.field4 = 0elseselect t2.field3 from table2 as t2 where t2.field4 = 0 or t2.field5 = 'X') |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-09 : 12:30:30
|
quote: Originally posted by paultech i think you may use case when select t1.field1, t1.field2 from table1 as t1where t1.field3 in(case when parameter1 = 'N' then select t2.field3 from table2 as t2 where t2.field4 = 0elseselect t2.field3 from table2 as t2 where t2.field4 = 0 or t2.field5 = 'X')good luckpaul Tech
did you test this at all?Case cant be used to manipulate program control flowcase is not a statement but rather its an expression which is used to manipulate the return value of column.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|