Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    9

    Unanswered: dynamic evaluation of expression operator (was "Substitution")

    Hi I am trying to do something like the following:

    DECLARE @Operator varchar(1)
    DECLARE @Rate float
    DECLARE @Quantity float
    DECLARE @Converted float

    SET @Quantity = 6
    SET @Operator = '/'
    SET @Rate = 2
    SET @Converted = 0

    @Converted = (@Quantity substituteTheValueOfThis(@Operator) @Rate)

    PRINT @Converted

    so that the output would be 3

    The reason I need to do it like this is that @Operator will change at runtime...

    Any suggestions appreciated, I have looked at EXEC sp_execsql but somehow can't get the syntax right.

  2. #2
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    I should try something like this ...

    First of all, the following

    DECLARE @Operator varchar(1)
    DECLARE @Rate float
    DECLARE @Quantity float
    DECLARE @Converted float

    SET @Quantity = 6
    SET @Operator = '/'
    SET @Rate = 2
    SET @Converted = 0

    Could be shortened to

    DECLARE
    @Operator varchar(1)
    , @Rate float
    , @Quantity float
    , @Converted float
    , @cmd varchar(1000)

    select @Quantity = 6, @Operator = '/', @Rate = 2, @Converted = 0
    set @cmd = 'select convert(decimal(38, 2), ' + convert(varchar, @Quantity) + convert(Varchar,@Operator) + convert(varchar, @Rate) + ')'
    exec (@Cmd)

    Sorry about the poor formatting, but this is cut n' pasted directly from Query Analyzer.

    Generally when you do calculations in SQL Server, your result will be with or without decimals depending on whether you supply decimals to the calculation at hand, hence
    3/2=1
    3.0/2=1.500000

  3. #3
    Join Date
    Oct 2003
    Posts
    9
    Thanks, this seems to work great when:

    select @Quantity = 6, @Operator = '/', @Rate = 2, @Converted = 0

    but returns 0 when

    select @Quantity = 6, @Operator = '/', @Rate = 100, @Converted = 0

    rather than

    .06

    Ah!! Is this what you mean at the end of your post by...

    "Generally when you do calculations in SQL Server, your result will be with or without decimals depending on whether you supply decimals to the calculation at hand, hence
    3/2=1
    3.0/2=1.500000"

    I am trying to do conversions between grams and kilograms to 3dp.. so values maybe something like...

    34.876 grams and i need to convert that to kilograms so would

    0.034876 which I would probably round to 0.035 kilograms

    This works but also value maybe

    6 grams to kilograms which at the moment returns 0

    Thanks for tips on the code and for making the title to thread more appropriate, I still have a lot to learn about transact sql and using this forum...
    Last edited by flutedoodler; 01-27-05 at 08:54.

  4. #4
    Join Date
    Oct 2003
    Posts
    9
    Sorry I forgot, can I also assign the result of the EXEC statement to a variable like this...

    @Result = EXEC (@cmd)

    I know this doesn't work but it illustrates what I am hoping to do!

    Thanks again!!!

  5. #5
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Cool Yes, you can, but you probably shouldn't ...

    Yes it IS possible to write dynamic SQL in a way that makes the execution of the string return a value (do a search on the words "dynamic SQL OUTPUT" on these boards and you'll find quite a few posts about it.
    However, dynamic SQL is not optimized by SQL Server (and couldn't be since it's not decided what it looks like until runtime.) and therefore it can be quite slow. If I understand your post correctly, basically what you are going for is a "mini-calculator"-procedure. You wanna send a first parameter, a operator and another operator to a procedure, and then do the evaluation and return the results in a proc.

    If that is the case, you could probably get away with
    ----- Start proc
    create procedure test as
    @Param1 float,
    @Param2 float,
    @Operator char(1)

    as

    select
    case @Operator
    when '-' then @Param1 - @Param2
    when '+' then @Param1 + @Param2
    when '*' then @Param1 * @Param2
    when '/' then @Param1 / @Param2
    else
    -1
    end
    return
    ----- End proc
    However I cannot see how this is needed, since the calculation should probably be done in the client. (Given that this procedure is in a serverside solution, which I assumed that it is ...)
    No need to do the dynamic SQL there. Perhaps I've simplified your problem and not considered all other factors, but as far as I can see my solution could work nicely, from a SQL point of view, but probably not from a architectual point of view. However, I choose not to discuss those matters here.

    Good luck.

  6. #6
    Join Date
    Oct 2003
    Posts
    9
    Thank you very very much! You've set me thinking about the placing of this routine and maybe as you say, it should really be in the middle tier.

    Thanks again for your time and very helpful comments

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •