Forum Discussion

SmithToronto's avatar
SmithToronto
Copper Contributor
Jun 21, 2024

Help in varchar column calculation

Hi, How I can calculate and get the calculation result of calc4 colum. Currently this is varchar but i want to calculate calc1+calc2+calc3 in calc4.

 

Create table Calculation (Calc1 numeric (10,2), calc2 numeric (10,2), Calc3 numeric (10,2), Calc4 varchar(40))

insert into Calculation (6,7,8, 'calc1+calc2+calc3')

insert into Calculation (9,9,9, 'calc1+calc2+calc3')

 

Desired result want:

Select * from Calculation

Calc1  calc2   Calc3   Calc4

6         7         8          22

9        9         9           27

6 Replies

Sort By
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 21, 2024

    SmithToronto , normally I would let the backend/frontend, in plain SQL it's to error-prone; but this should work:

    Create table ##Calculation (Calc1 numeric (10,2), calc2 numeric (10,2), Calc3 numeric (10,2), Calc4 varchar(40))

insert into ##Calculation 
values (6,7,8, 'calc1+calc2+calc3');

DECLARE @sql nvarchar(4000);
SET @sql = (SELECT N'SELECT C.Calc1, C.Calc2, C.Calc3, ' + C.Calc4 + N' ' +
                   N'FROM ##Calculation AS C'
            FROM ##Calculation AS C);
EXEC sp_executesql @sql

GO

DROP TABLE ##Calculation;
GO

Resources