more efficient way of executing a procedure?
-
I have the following procedure:
Extract_Report(@ReportType varchar(10)) as
if @ReportType = 'A'
begin
select ColumnA,Count(*)
from test_table
group by ColumnA
end
if @ReportTyep = 'B'
begin
select ColumnB,count(*)
from test_table
group by ColumnB
end
etc. etc.
is there a more elegant way of select different columns depending on the input. (may be using Case ?)
thank you in advance
-
For my opinion, if you have more than 5 different columns as the input, you can consider using dynamic SQL. Otherwise it's not easy to maintain the code.
-
Well, it depends
One way that may be more 'elegant' is simply to split different select statements into different procedures. There is no black or white answer to this question. Sometimes you can have a single proc with branching code, other times that is not such a good idea. Main decider is how similar or different the branches are.. among other things
/Kenneth
-
Try this:
select
case when @ReportType = 'A' then ColumnA else null end,
case when @ReportType = 'B' then ColumnB else null end,
count(*) from test_table
group by
case when @ReportType = 'A' then ColumnA else null end,
case when @ReportType = 'B' then ColumnB else null end
-
don't you mean this ?? :
select
case @ReportType
when 'A' then ColumnA
when 'B' then ColumnB
ELSE NULL
END,
count(*) from test_table
group by
case @ReportType
when 'A' then ColumnA
when 'B' then ColumnB
ELSE NULL
END
-
I guess I do now
But your solution doesn't work if columnA is an int column and columnB is a varchar column... it fails if @ReportType='B'
-
Just have to cast everything to varchar...
-
Sure, but that may not be what you want...
But the columns probably have the same datatype, and then your solution is definitely preferrable. -
We are on the path of redefining 'elegant' now, aren't we..?
/Kenneth
-
Worth a shot if it means avoiding dynamic sql
. -
I had to do this for many years. There is no such thing as packages in Oracle. I could never figure out another way, so if there is one I' like to know myself...
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply