dynamic FROM statement - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

dynamic FROM statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-07-09 : 10:56:22

Sal writes "I'd like have a dynamic FROM list in my stored procedure -- This is a simple search across certain tables depending on a flag passed to the procedure -- The tables are identical in structure. One is a commercial database of contacts and one is a private database. The search engine needs to search either one, the other or both.

I've tried this :

@sstring varchar(50)
@stype int

SELECT F1,F2,F3

FROM

CASE WHEN @stype = 1 then searchall
CASE WHEN @stype = 2 then searchschools
CASE WHEN @stype = 3 then searchcontacts

Where SCHOOLNAME Like ...

I know I can have separate SELECT statements with IF s in between but case would be much more elegant.

Any help would be appreciated."

Kristen
Test

22859 Posts

Posted - 2004-07-09 : 12:13:42

You can only do it that way with dynamic SQL - such as:


DECLARE @strSQL varchar(8000)

SELECT @strSQL = 'SELECT F1,F2,F3 FROM '
	+ CASE	WHEN @stype = 1 then 'searchall'
		WHEN @stype = 2 then 'searchschools'
		WHEN @stype = 3 then 'searchcontacts'
		ELSE 'Bad_stype'
		END
EXEC (@strSQL)

Kristen

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-09 : 23:54:09

[code]

create table c ( v1 int, v2 int)
insert into c
select 0,2 union all
select 4,6

create table p ( v1 int, v2 int)
insert into p
select 1,3 union all
select 5,7

declare @stype tinyint
set @stype = 1

select v1,v2
from
(
select
case when @stype = 1 then 1 else 2 end stype, v1, v2
from c
union all
select
case when @stype = 1 then 1 else 3 end stype, v1, v2
from p
) a
where stype = @stype
--and v1 like ...

drop table c,p[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources