Dynamic query

Databases
1

I have a database table month_values

this table has 12 columns… columns are jan , feb , march , …decm.

I have data values for each months.

I’m writing store procedure. I want to sum values between any two input month columns.

Example Input :

SP_MONTH_VALS( feb,sept)

Expected Output:

output for the above stored procedure should be sum values for month columns feb,march,april,may,june,july,aug,sept.

Problem:

my problem is …As you can see this is going to be dynamic query…because we may pass any two month columns to the store procedure input.

Can you please suggest how to build dynamic query here based on input selection ?

Do I need to use a CASE here ? how do I proceed ?

2

i know you’re going to get upset at this, but it needs to be said – your table design is the cause of your difficulty

if you had one row per month’s data, where the month number (1 through 12) was part of the key, then the query would be trivial…

SELECT SUM(amt)
  FROM daTable
 WHERE mth >= @startmonth
   AND mth <= @endmonth

here @startmonth and @endmonth are placeholders (not php variables ;)) for the two months passed in to your stored proc

3

No. I dont have. I don’t want to change the design also…this is impossible.

Do you suggest any workaround with this existing design ?

4

fine, do it your way :slight_smile:

stored procedures allow the use of the IF statement

use it… copiously

5

I know it would be clumsy.

  1. design can not be changed because its already loaded with data.
  2. its a huge database with many tables having relationships with each other.
  3. system is stable with user input in the UI level.
  4. database designed by a certified DBA. Its not easy to comment on design without looking at the complete database design and complicacy. I also have least control altering any database design changes here.

Now having said these … I understand if is an alternative way out…but I’m looking out possibilities for a better approach.

Is not that going to bring many IF’s here ? Can we think about minimizing IF here ?

How you are planning to mark the range ?

6

Personally, due to your limitations, I’d argue going on a software approach instead of a query approach.

Let the UI collect the start and ending months, but return the entire 12 months back in the query and then let the programming language figure out which columns to add together. Otherwise you are looking at a very bulky, hard to maintain stored procedure.

7

I liked this approach. Yes. I do have a control on JSP / Java side . I hold UI data in a java variable.

I’m not clear … are you suggesting to build query in java side and then pass the query to the stored procedure ? OR are you saying to collect how many months needs to be passed to the store procedure ?

where the select query will built up ?

Please clarify .

8

I am saying, let the UI collect the start and end month, run a generic query that returns all months, then let the code loop through the results and sum up the appropriate columns based on the start and end month.

You could have the UI build the query too (if that is acceptable in your work environment) and run an ad-hoc query instead of a stored procedure. Either way, doing this in a stored procedure (in my opinion) is a mistake due to the restraints on the table design.

9

my comments in the above

10

Okay. Keep in mind you may be opening up a security issue with utilizing a stored procedure to accept a query. As a developer could send whatever query they would want through that stored procedure and depending on the role associated to the stored procedure may have more access than they should.

If you only have them send in the SUM equation, you could likely concatenate the rest of the query so the stored procedure limits the return data to your specific table(s).

11

this is what you need
I created just sample table and added test data into

create table #T
(
Jan int,
Feb int,
Mar int,
Apr int,
May int,
June int,
July int,
Aug int,
Sept int,
Oct int,
Nov int,
Dec int
)

insert into #T
values(12,15,48,74,636,89,12,45,54,88,88,77)
insert into #T
values(2,1,8,4,63,8,1,5,4,8,8,7)

so, select statement like below
SELECT * FROM #T
produce output…
Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec
12 15 48 74 636 89 12 45 54 88 88 77
2 1 8 4 63 8 1 5 4 8 8 7

Now time to create your store proc

DECLARE @startMonth as int, @endMonth as int

set @startMonth=2

set @endMonth=5

;
with t as
(
select 1 as mnth, jan as cnt from #T
union
select 2 as mnth, feb as cnt from #T
union
select 3 as mnth, Mar as cnt from #T
union
select 4 as mnth, Apr as cnt from #T
union
select 5 as mnth, May as cnt from #T
union
select 6 as mnth, June as cnt from #T
union
select 7 as mnth, July as cnt from #T
union
select 8 as mnth, Aug as cnt from #T
union
select 9 as mnth, Sept as cnt from #T
union
select 10 as mnth, oct as cnt from #T
union
select 11 as mnth, nov as cnt from #T
union
select 12 as mnth, dec as cnt from #T
)
select SUM(cnt) FROM t
WHERE mnth BETWEEN @startMonth AND @endMonth

I declare variables (you should have them as stored procedure parameters) and give them values just for test

that produce result

total
849

which you looking for.

12

discombobulating the badly designed table on the fly – i love it :slight_smile:

:award:

13

This process assumes SQL Server right? As I don’t think Oracle or MySQL support CTEs (I could be wrong)

14

CTEs can always be re-written as POS (plain old sql)

15

So, change with to

select SUM(cnt) FROM
(
select 1 as mnth, jan as cnt from #T
union
select 2 as mnth, feb as cnt from #T
union
select 3 as mnth, Mar as cnt from #T
union
select 4 as mnth, Apr as cnt from #T
union
select 5 as mnth, May as cnt from #T
union
select 6 as mnth, June as cnt from #T
union
select 7 as mnth, July as cnt from #T
union
select 8 as mnth, Aug as cnt from #T
union
select 9 as mnth, Sept as cnt from #T
union
select 10 as mnth, oct as cnt from #T
union
select 11 as mnth, nov as cnt from #T
union
select 12 as mnth, dec as cnt from #T
) t
WHERE t.mnth BETWEEN @startMonth AND @endMonth

it is supports by mySQL and Oracle