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.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|||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... :)|||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!!!|||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. :cool:|||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 :)