CODEWITHSUNDEEP
sqlsql-serversql-server-2012
StackUser
StackUser

Reputation: 5398

Evaluate expression stored in Varchar Column directly in the "select" statement

I have a table named PriceDetails with three columns "price","discount formula" and "finalprice". I have to calculate final price based on "Price" and "discount formula" columns.

My table looks like this,

Price   DiscountFormulae
100     100*(3/100)
200     200*(1.1/100)+200*(5/100)
300     300*(1/100)+300*(3/100)+300*(2/100)
400     400*(7/100)+400*(6.6/100)+400*(5.5/100)+400*(4/100)

I want to calculate final price. The formula would be "Price"-"DiscountFormulae"

for example

100 - (100*(3/100)) =97

My Expected Output would be

Price   DiscountFormulae FinalPrice
100     100*(3/100)         97

Note: My DiscountFormulae Column is Varchar(1000)

How do I get this. Please help me.

Upvotes: 1

Views: 371

Answers (2)

Backs
Backs

Reputation: 24903

Not very good solution, but it shows, how difficult is it:

CREATE TABLE #temp (Price int, DiscountFormula Varchar(1000))
INSERT INTO #temp 
(#temp.Price, #temp.DiscountFormula)
VALUES
(100,'100.0*(3.0/100)'),
(200,'200.0*(1.1/100)+200*(5.0/100)'),
(300,'300.0*(1.0/100)+300*(3.0/100)+300*(2.0/100)'),
(400,'400.0*(7.0/100)+400*(6.6/100)+400*(5.5/100)+400*(4.0/100)')

DECLARE @price int

DECLARE cur CURSOR FOR SELECT t.Price FROM #temp AS t
OPEN cur
FETCH NEXT FROM cur INTO @price
WHILE (@@FETCH_STATUS = 0)
BEGIN

    DECLARE @sql nvarchar(MAX) = N'
    SELECT 
        t.Price,
        t.DiscountFormula,
        ' + (SELECT TOP 1 DiscountFormula FROM #Temp WHERE Price = @price) + N' as Value
    FROM #temp AS t
    WHERE Price = ' +CONVERT(nvarchar(max),@price)

    EXEC sp_executesql @sql

    FETCH NEXT FROM cur INTO @price
END

CLOSE cur

DROP TABLE #temp

By the way, use float values in formula.

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

You could do it in a stored procedure, like:

But dynamic SQL, like exec or sp_executesql, is not allowed in user defined functions.

DECLARE @calc varchar(max)
SET @calc = '100*(3/100)'

DECLARE @sql nvarchar(max)
DECLARE @result float
SET @sql = N'SET @result = ' + @calc
exec sp_executesql @sql, N'@result float output', @result out
SELECT @result

Upvotes: 0

Related Questions