August 14, 2008 at 6:55 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 4:18 am
Hi,
Your problem is that EXEC does not change the @SQL variable to be the resultset. It's still the query you put together, hence why you can't convert it to a money datatype. Run the following:
DECLARE @SQL VARCHAR(100)
SET @SQL = 'SELECT 1 + 2'
EXEC(@SQL)
SELECT @SQL
You should see two result sets, one of 3 (From the Exec), one of 'SELECT 1 + 2' which is the value of @SQL.
Ideally, you should change the Fee1, Fee2, Fee3 table to be a table of fees and perhaps fee types, that has a key to the original table. That way you can add any number of fees, without all this trouble. Take a look at http://en.wikipedia.org/wiki/Database_normalization.
If you really have to do it this way, you could use sp_ExecuteSQL - It has an output parameter option, so you could retrieve your SUM result an insert it. Look at http://www.sommarskog.se/dynamic_sql.html#sp_executesql for a tutorial.
BUT - Please read the rest of Sommarskog's treaty on dynamic SQL, as you are going down a road where you will have many more problems in the future. Try and make changes now so you don't require dynamic SQL 🙂
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply