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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Can I avoid duplicating query ? Conditional test

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 t1
where 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 t1
where t1.field3 in
(select t2.field3 from table2 as t2
if 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 t1
inner join table2 as t2
on t1.field3 = t2.field
where ((t2.field4 = 0 and parameter1 = 'N')
or (t2.field5 = 'X'))
[/code]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 t1
where t1.field3 in
(case when parameter1 = 'N' then
select t2.field3 from table2 as t2 where t2.field4 = 0

else
select t2.field3 from table2 as t2 where t2.field4 = 0 or t2.field5 = 'X'
)

Go to Top of Page

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 t1
where t1.field3 in
(case when parameter1 = 'N' then
select t2.field3 from table2 as t2 where t2.field4 = 0

else
select t2.field3 from table2 as t2 where t2.field4 = 0 or t2.field5 = 'X'
)

good luck

paul Tech


did you test this at all?
Case cant be used to manipulate program control flow
case is not a statement but rather its an expression which is used to manipulate the return value of column.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -