August 14, 2008 at 5:51 pm
create table mytable (fee1 money, fee2 money, fee3 money)
create table mytotals (feetotal money)
insert into mytable values (32, 11, 24)
insert into mytable values (12, 25, 44)
insert into mytable values (16, 14, 42)
I need to summarize totals for each column and insert them into another table, into a field of money datatype.
The problem is I need (or at least I think I need) to use dynamic SQL because the number of 'fee' fields could be changing over time.
Now there are 3 fields, tomorrow there could be 4 or 5.
My code:
declare @i int
declare @err int
declare @sql varchar(200)
set @i = 1
set @err = 0
while @err = 0
begin
set @sql = 'select sum(fee' + cast(@i as varchar) + ')
from mytable'
exec(@sql)
insert mytotals values (@sql)
end
This code gives me an error trying to convert varchar (@sql) into money.
Thanks in advance!
August 15, 2008 at 6:40 am
I am going to answer your specific question, but first I am going to try to give you some advice that I think will save you some pain down the line first. So please stick with me.
Do you have the option of re-designing the database? The current table design violates Normal Form. You really should have a fees table that allows you to store fees as rows. I am assuming each different fee is a different type of fee. For example a school may have Lab Fees, Sports Fees, Drama Fees. In this instance I would have fees (id, description, amount), students (id, name, etc...), student_fees(id, student_id, fee_id), student_fee_receipts(student_fee_id, amount). In this design you can have N fees and the same query will work for all. For example this query will show the total fee amount owed by a student:
[font="Courier New"]SELECT
student_id,
SUM(amount) AS total_owed
FROM
student_fees SF JOIN
fees F ON
SF.fee_id = F.fee_id
GROUP BY
SF.student_id[/font]
And this gets by student and fee:
[font="Courier New"]SELECT
SF.student_id,
SF.fee_id,
SUM(amount) AS total_owed
FROM
student_fees SF JOIN
fees F ON
SF.fee_id = F.fee_id
GROUP BY
SF.student_id,
SF.fee_id[/font]
Anyway all that because I hate to see someone head down the path you are on IF it can be avoided. Now on to your question:
To do what you want you would do:
Insert Into mytotals
Exec (@sql)
What you are doing is trying to put the SQL String you have built into the total column of your mytotals table, not the results of the statement. You do realize that as you add fee columns you are going to have to edit your code to change the number of times you loop so get a total for each fee column, so you might as well write standard SQL using a union and just add a new query to the union when you add a new fee column.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 6:55 am
Thank you!
This helped a lot!
Now, I have another task to do - I need to summarize values of all the 'fee' fields for each row.
Now there three 'fee' fields, but since I don't know how many of them could be there in the future, I need to use dynamic SQL to do that.
Can't figure out how.
August 19, 2008 at 1:35 pm
Jack's got it right: normalize! If you can't, then you are going to have to use dynamic SQL. It's not that dynamic SQL is "bad", it has it's uses, it's just that this doesn't appear on the surface to be a good fit. Why don't you know how many "fee" columns there will be? Is this a table coming from an external source?
August 19, 2008 at 4:39 pm
Yes, exactly so - it's a table coming from an external source.
August 19, 2008 at 6:25 pm
So how are you accessing the external source?
There really isn't an easy way to do it, nor is there an easy way to explain how I'd do it.
If you have the appropriate access you could use INFORMATION_SCHEMA.COLUMNS which is an ANSI Standard view of all the columns in the database. So you'd have a query like:
Select column_name from information_schema.columns where table_name = [table_name] and column_name like 'fee%'
You could use a cursor and then within your cursor loop build your sql string. Like this (assumes you already have the cursor and base SQL prepared):
While @@FETCH_STATUS = 0
BEGIN
Set @Select = ' Select Sum(' + @column + ') From table where criteria'
Set @SQL = @SQL + ' Union ' + @Select
Fetch Next From [cursor] Into
@column
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2008 at 8:04 pm
Coming from an external source, I would normalize at the data import. If you use dynamic sql within the data import, you will save needing it to query your data... and allow SQL query optimizer to do it's job.
Again, just my 2 cents.
August 20, 2008 at 5:43 am
Spend some time in converting the external data as pr YOUR requirement and avoid further pains.
August 20, 2008 at 6:12 am
If this is a one shot conversion or operation, you may want to add a computed column to the table
alter table [yourtable] add CompCol_TheSum as (isnull(colA,0) + isnull(colB,0) + isnull(colC,0))
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply