Dynamic SQL, problem converting varchar into money datatype

  • levsha_z

    SSC Eights!

    Points: 862

    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!

  • Leo Mepham

    Ten Centuries

    Points: 1045

    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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply