Selecting from different tables

[manning_news]

Using SQL2000. How do I format my select statement to choose one out
of 24 different tables? Each table is slightly different and I was
hoping I could use one select statement and format it on-the-fly
instead of using 24 different ones. I had in mind using a case
statement, something like this:

select * from
case when <input parameter> = 'something1' then tblSomething1
case when <input parameter> = 'something2' then tblSomething2
...and so on...

Thanks for any help.

[Erland Sommarskog]

(ma**********@hotmail.com) writes:

Using SQL2000. How do I format my select statement to choose one out
of 24 different tables? Each table is slightly different and I was
hoping I could use one select statement and format it on-the-fly
instead of using 24 different ones. I had in mind using a case
statement, something like this:

select * from
case when <input parameter> = 'something1' then tblSomething1
case when <input parameter> = 'something2' then tblSomething2
...and so on...

You can use dynamic SQL, but it's not sure that is a good idea.

One has to understand that a table definition is a little different from
a record or struct definition in a traditional language. A table comes
with indexes and statistics. Two table can look identical, yet the
optimizer may choose two different ways to compute:

SELECT typecol, COUNT(*)
FROM tbl1
WHERE grouptype = 'ABC'
GROUP BY typecol

SELECT typecol, COUNT(*)
FROM tbl2
WHERE grouptype = 'ABC'
GROUP BY typecol

Say both have a non-clustered index on grouptype and typecol is not
in the index. But in tbl1 only 3 values (of 4000) has grouptype = 'ABC'
whereas in tbl2 there are 3000 (of 4000) with that value. The optimizer
will pick different plans for these.

This is why there is no built-in construct in the language for choosing
one of many similar tables. When it comes to building the query plan,
each table is unique.

As I said, you could use dynamic SQL, and I have longer article on this
topic on my web site. http://www.sommarskog.se/dynamic_sql.html. The
section that is likely apply directly to your situation is
http://www.sommarskog.se/dynamic_sql.html#Dyn_table and the following
section.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[manning_news]

Thanks for your reply. I think I'll stick with separate select
statements for all 24 tables.

Erland Sommarskog wrote:

(ma**********@hotmail.com) writes:

Using SQL2000. How do I format my select statement to choose one out
of 24 different tables? Each table is slightly different and I was
hoping I could use one select statement and format it on-the-fly
instead of using 24 different ones. I had in mind using a case
statement, something like this:

select * from
case when <input parameter> = 'something1' then tblSomething1
case when <input parameter> = 'something2' then tblSomething2
...and so on...

You can use dynamic SQL, but it's not sure that is a good idea.

One has to understand that a table definition is a little different from
a record or struct definition in a traditional language. A table comes
with indexes and statistics. Two table can look identical, yet the
optimizer may choose two different ways to compute:

SELECT typecol, COUNT(*)
FROM tbl1
WHERE grouptype = 'ABC'
GROUP BY typecol

SELECT typecol, COUNT(*)
FROM tbl2
WHERE grouptype = 'ABC'
GROUP BY typecol

Say both have a non-clustered index on grouptype and typecol is not
in the index. But in tbl1 only 3 values (of 4000) has grouptype = 'ABC'
whereas in tbl2 there are 3000 (of 4000) with that value. The optimizer
will pick different plans for these.

This is why there is no built-in construct in the language for choosing
one of many similar tables. When it comes to building the query plan,
each table is unique.

As I said, you could use dynamic SQL, and I have longer article on this
topic on my web site. http://www.sommarskog.se/dynamic_sql.html. The
section that is likely apply directly to your situation is
http://www.sommarskog.se/dynamic_sql.html#Dyn_table and the following
section.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ford Desperado]

let's say I have an SP like this:
if <input parameter> = 'something1' then
SELECT typecol, COUNT(*)
FROM tbl1
WHERE grouptype = 'ABC'
GROUP BY typecol
else
if <input parameter> = 'something2' then
SELECT typecol, COUNT(*)
FROM tbl2
WHERE grouptype = 'ABC'
GROUP BY typecol

in the body of the stored procedure. How could I look up the execution
plans for these 2 selects?

TIA

[Erland Sommarskog]

Ford Desperado (fo************@yahoo.com) writes:

let's say I have an SP like this:
if <input parameter> = 'something1' then
SELECT typecol, COUNT(*)
FROM tbl1
WHERE grouptype = 'ABC'
GROUP BY typecol
else
if <input parameter> = 'something2' then
SELECT typecol, COUNT(*)
FROM tbl2
WHERE grouptype = 'ABC'
GROUP BY typecol

in the body of the stored procedure. How could I look up the execution
plans for these 2 selects?

The simplest is to run the procedure from Query Analyzer, and prior to
that press CTRL/K. That will give you a tab with the execution plans.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp