Conditional FROM/WHERE

[Craig Somberg]

SQL 7

Hi All !!

I want to know how I can create conditional FROM WHERE clauses like below ..

SELECT X,X,X
FROM
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
WHERE
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END

Thoughts ?

Thanks

[karl]

I've seen the case statement being used in the where clause but not in the from clause so I'm not sure that would work.

In this case I think your best bet is to build up a string and then use the stored procedure sp_executesql to execute that string.

e.g. pseudo code
declare @string varchar(...)

if intAltSQL > 0
@string = 'select .........'
exec sp_executesql @string
else
@string = 'select.....'
exec sp_executesql @string
end

hope this helps,

Karl



------------
Craig Somberg at 4/13/01 1:49:34 PM

SQL 7

Hi All !!

I want to know how I can create conditional FROM WHERE clauses like below ..

SELECT X,X,X
FROM
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
WHERE
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END

Thoughts ?

Thanks

[Craig]

Thanks for the reposne. I was looking to maximize the sql statement in a sp rather than having to build a dynamic sql statement. I figured the gain would be much better.

Thanks.


------------
karl at 4/13/01 4:00:01 PM

I've seen the case statement being used in the where clause but not in the from clause so I'm not sure that would work.

In this case I think your best bet is to build up a string and then use the stored procedure sp_executesql to execute that string.

e.g. pseudo code
declare @string varchar(...)

if intAltSQL > 0
@string = 'select .........'
exec sp_executesql @string
else
@string = 'select.....'
exec sp_executesql @string
end

hope this helps,

Karl



------------
Craig Somberg at 4/13/01 1:49:34 PM

SQL 7

Hi All !!

I want to know how I can create conditional FROM WHERE clauses like below ..

SELECT X,X,X
FROM
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
WHERE
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END

Thoughts ?

Thanks

[Me]

Try a derived table. You're doing a union of two queries, making sure one of them returns no rows.

declare @intAltSQL int
set @intAltSQL = -1

select num
from
(select 2 num -- this set is greater than zero
where @intaltsql > 0
union all
select 3 num -- this set is for intalt = 0
where @intaltsql = 0) derived


------------
Craig Somberg at 4/13/01 1:49:34 PM

SQL 7

Hi All !!

I want to know how I can create conditional FROM WHERE clauses like below ..

SELECT X,X,X
FROM
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END
WHERE
CASE @intAltSQL > 0 Then Blah Blah Blah END
CASE @intAltSQL = 0 Then Blah END

Thoughts ?

Thanks

[Me]

Forgot to mention: if your query is simple, you don't need the derived table, just the select/union all/select.


------------
Me at 4/14/01 8:47:41 PM

Try a derived table. You're doing a union of two queries, making sure one of them returns no rows.

declare @intAltSQL int
set @intAltSQL = -1

select num
from
(select 2 num -- this set is greater than zero
where @intaltsql > 0
union all
select 3 num -- this set is for intalt = 0
where @intaltsql = 0) derived