Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: Calculating a string

    Hi guys,

    this is my first message on the forum, hope you can help.

    I am trying to calculate a string.

    ie

    declare @Mycalcstring varchar(50)

    set @Mycalcstring = "(10-5)"

    How do I calculate mystring to get the result 5.

    @Mycalcstring is built of parameters which is why it is in string format

    any offers

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this?

    create table #tmp(result int)
    declare @Mycalcstring varchar(50)
    set @Mycalcstring = '(10-5)'
    insert #tmp
    exec('select '+@Mycalcstring)
    select * from #tmp

  3. #3
    Join Date
    Sep 2003
    Posts
    4
    Thank you for responding,

    I have tried that it query analyzer and that worked BUT
    I then put in into my trigger and it fails, any clues.

    Also do I have to DROP the table after use

    Thank you in advance

    Originally posted by snail
    What about this?

    create table #tmp(result int)
    declare @Mycalcstring varchar(50)
    set @Mycalcstring = '(10-5)'
    insert #tmp
    exec('select '+@Mycalcstring)
    select * from #tmp

  4. #4
    Join Date
    Sep 2003
    Posts
    4
    PS I get the message incorrect syntax near SELECT.

    Its as if it doesn't like the varchar being passed to it.

    Using this:

    create table #tmp(result int)
    declare @Mycalcstring varchar(50)
    set @Mycalcstring = '(10-5)'
    insert #tmp
    exec('select '+@Mycalcstring)
    select * from #tmp

    Will work but as soon as I try to pass a built string it errors.

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Assume that you will insert row by row:

    create table test(id int,code varchar(500),result varchar(25))
    go
    create trigger i_test on test
    for insert
    as
    declare @line varchar(500)
    create table #tmp(result int)
    select @line = code from inserted
    insert #tmp
    exec('select '+@line)
    update test set result=(select max(result) from #tmp)
    where id=(select max(id) from inserted)
    go
    insert test select 1,'(45-32)',0
    select * from test
    insert test select 2,'(45-32)*65',0
    select * from test

    You do not need to drop #tmp - it exists only in trigger

  6. #6
    Join Date
    Sep 2003
    Posts
    4

    Talking Thank You Snail

    Thank you Snail

    That worked a treat.

    You are truly a genius.

    We are not worthy.

Posting Permissions

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