CODEWITHSUNDEEP
sybase
Neerav
Neerav

Reputation: 1429

Is it possible to use a table based on case statement?

I have a procedure which looks like this.

CREATE PRODEDURE get_count
    @rdr CHAR(3)
AS
    SELECT t.cnt FROM (
    SELECT COUNT(*) cnt, 'X' rdr  FROM table_X
    UNION ALL
    SELECT COUNT(*) cnt, 'Y' rdr FROM table_Y
    UNION ALL
    SELECT COUNT(*) cnt , 'Z' rdr  FROM table_Z) t
    WHERE t.rdr = @rdr

Is it possible to use a case statement or some other Sybase trick that I am unaware of to optimize above procedure? So that, it uses the table_X when you pass in 'X', table_Y when you pass 'Y' and so on..The only motivation for optimization is because the individual count() operations take a lot of time and every time I make a call it comes to a standstill...

Upvotes: 0

Views: 241

Answers (2)

Gopal Sanodiya
Gopal Sanodiya

Reputation: 106

you can do it in two ways

1- add query in if else statement

if @var="X"
select count(*) from table_X

if @var="Y"
select count(*) from table_Y

if @var="Z"
select count(*) from table_Z

OR use dynamic SQL-- pass table name as input
declare @a varchar(30)
select @a= "table_name"
EXEC ("select count(*) from "+@a)

Thanks,

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180787

Run a SELECT COUNT query on each individual table and collect the counts. Then add all the counts together to get the final result.

While this comes at the cost of running three queries instead of one, it eliminates the UNIONs and allows you to use an index on the rdr field for each table.

Upvotes: 0

Related Questions