Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121

    Unanswered: Building a calculation string

    Hiya folks,
    I'm trying to build up a calculation string the values and operators will (eventually) be held in a SQL table. But for now i'm just testing it using the query analyzer. Please see example code below:

    Declare @MyFirstNo varchar(50)
    Declare @MySecondNo varchar(50)
    Declare @MyStringPart varchar(50)
    Declare @MyCalcString varchar(50)
    Declare @MyResult varchar(50)

    SET @MyCalcString = ''
    SET @MyFirstNo = 10
    SET @MySecondNo = 3
    SET @MyStringPart = '/'

    SET @MyCalcString = @MyCalcString + @MyFirstNo
    SET @MyCalcString = @MyCalcString + @MyStringPart
    SET @MyCalcString = @MyCalcString + @MySecondNo

    create table #tmp1(result decimal(18,5))
    insert #tmp1
    exec('select '+@Mycalcstring)
    select @MyResult = result from #tmp1
    drop table #tmp1

    Print @MyResult



    So the calculation is : 10/3. Which results in an answer of 3.00000. Which is obviously wrong. If I declare the variables as 'decimal' then I can't build the calcation string, with an error of 'Error converting varchar to numeric'.

    Any offers?

    Love and peace

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Building a calculation string

    Declare @MyFirstNo varchar(50)
    Declare @MySecondNo varchar(50)
    Declare @MyStringPart varchar(50)
    Declare @MyCalcString varchar(50)
    Declare @MyResult varchar(50)

    SET @MyCalcString = ''
    SET @MyFirstNo = 10.0
    SET @MySecondNo = 3.0
    SET @MyStringPart = '/'

    SET @MyCalcString = @MyCalcString + @MyFirstNo
    SET @MyCalcString = @MyCalcString + @MyStringPart
    SET @MyCalcString = @MyCalcString + @MySecondNo

    create table #tmp1(result decimal(18,5))
    insert #tmp1
    exec('select '+@Mycalcstring)
    select @MyResult = result from #tmp1
    drop table #tmp1

    Print @MyResult

  3. #3
    Join Date
    Sep 2003
    Location
    Birmingham, UK
    Posts
    121
    Thanks for taking the time to reply, (although it took me a while to spot what you'd changed!!!). Seems to have done the trick, now comes the extensive testing! Any problems and I'll let you know.

    Love and peace

Posting Permissions

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