sql server programming: You may want to check out: www.sommarskog.se/dyn-search.html

--
Anith

HI all,

I am trying to build aconditional select statement. I am hoping to stay
clear of dynamic sql, as the query, or at least SP is quite long.
In essance there is one parameter with a select statement, the a bunch of
joins and unions, based on the value of the select statement, THe example
following is rather simple compared to the complexity that I am dealing
with.
I know this is not correct sql, but I wanted to show what type of thing I am
trying to achieve.
Is there any other way of achieving this

select * from table1 where field1 in
(
if parameter1 = "value1"
begin
select field1 in table2 where field2 = somevalue
end
)

(
if parameter1 = "value2"
begin
select field1 in table3 where field2 = somevalue
end
)


(
if parameter1 = "value3"
begin
select field1 in table4 where field2 = somevalue
end
)
union
if parameter = "Value1"
begin
select * from table5 where field1 in
(
if parameter1 = "value1"
begin
select field1 in table6 where field2 = somevalue
end
)

(
if parameter1 = "value2"
begin
select field1 in table7 where field2 = somevalue
end
)


(
if parameter1 = "value3"
begin
select field1 in table8 where field2 = somevalue
end
)
end

union

if parameter = "Value1"
begin
select * from table9 where field1 in
(
if parameter1 = "value1"
begin
select field1 in table10 where field2 = somevalue
end
)

(
if parameter1 = "value2"
begin
select field1 in table11 where field2 = somevalue
end
)


(
if parameter1 = "value3"
begin
select field1 in table12 where field2 = somevalue
end
)
end

.....
.....
.....



Robert

Thanks.
Good reading

RObert

[quoted text, click to view]