Post reply

Conversion failed when converting the varchar value '1*2*3' to data type int.

  • January 8, 2014 at 11:11 am

    #302926

    Hi,

    I have been trying to write a stored procedure to get value from a formula. I have a formula stored in my table column and I need to replace the variable with a numeric value and get back with calculation.

    For example- I have the formula stored as in a table column as varchar : V6 * 1.103 * 0.0078

    in runtime I get the V6 to be substituted with a value say suppose = 684. So, I need 684 * 1.103 * 0.0078 = 5.88(decimal rounded to 2 values)

    I tried many ways, but I did not get to a solution. The following code is a sample of what I tried:

    DECLARE @MYFOR AS VARCHAR(100)

    SET @MYFOR ='V6*2*3'

    SELECT REPLACE(@MYFOR,'V6','1') - this works and replaces V6 with 1- output is 1*2*3 , but I want with multiplication . I want my output as 6.

    select Cast((REPLACE(@MYFOR,'V6','1') as int) - this fails

    Select Convert(integer,@MYFOR) - this fails.

    Please help me...How I do get to replace the variable in the formula and then do the multiplication? Please guide me....I really appreciate it...

  • January 8, 2014 at 11:54 am

    #1678533

    You can't assign the result of a formula directly to a variable. You would need to use dynamic code.

    Here's an example:

    DECLARE @MYFOR AS NVARCHAR(100), @Result int

    SET @MYFOR ='V6*2*3'

    SELECT @MYFOR = 'SELECT @Output = ' + REPLACE(@MYFOR,'V6','1')

    SELECT @MYFOR

    EXEC sp_executesql @MYFOR, N'@Output int OUTPUT', @Output = @Result OUTPUT

    SELECT @Result

    And here's the reference:

    http://technet.microsoft.com/en-us/library/ms188001.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 9, 2014 at 12:18 pm

    #1678935

    Thanks for the answer and also for the link. It will help me understand string manipulations in SQL.

  • January 9, 2014 at 12:39 pm

    #1678944

    I hope that it helps you. If you have any more questions, feel free to ask 😉

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 4 posts - 1 through 4 (of 4 total)

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