This is difficult to put into words. I have a very long Stored Procedure (SP) for a collection of reports. The SP has 6 unioned selects in it and also has 6 optional parameters. By passing yes or no into each of those 6 parameters I can have the SP return data from only the unions that I want. This works great except when I pass in a NO parameter for one of the selects that select does not return any records(by design) but it is still executed and this is causing a big performance hit.
So, how can I acheive the same results w/o the all the selects being executed. The sample below probably explains the issue a little better.
**********************
In the below example I would really like to have the dog select not even be executed but it is.
***********************
Declare @cat_flag varchar(10)
Declare @dog_flag varchar(10)
set @cat_flag = 'yes'
set @dog_flag = 'no'
Begin
select 'dog'
where @dog_flag ='yes'
Union All
select 'cat'
where @cat_flag = 'yes'
end
**************************
Thanks
Patrick
So, how can I acheive the same results w/o the all the selects being executed. The sample below probably explains the issue a little better.
**********************
In the below example I would really like to have the dog select not even be executed but it is.
***********************
Declare @cat_flag varchar(10)
Declare @dog_flag varchar(10)
set @cat_flag = 'yes'
set @dog_flag = 'no'
Begin
select 'dog'
where @dog_flag ='yes'
Union All
select 'cat'
where @cat_flag = 'yes'
end
**************************
Thanks
Patrick