SQL字符串内运算

[Freeid_shi]

CREATE TABLE #TB (ZHI int,SEC VARCHAR(40))
INSERT #TB
SELECT 2,'*10*1.06/1000000' UNION ALL
SELECT 3,'*12*1.06/1000000' UNION ALL
SELECT 15,'*8*2.78/1000000'

如果求到ZHI与SEC的乘积和，
SEC这个字段是varchar型的

[Tiger_Zhao]

DECLARE @sql varchar(max)

SET @sql = ''

SELECT @sql = @sql + '
UNION ALL
SELECT CONVERT(DECIMAL(20,10),'+CONVERT(varchar(11),ZHI)+SEC+') AS V'
  FROM #TB

SET @sql = STUFF(@sql,1,13,'')

PRINT @sql

EXEC(@sql)

PRINT输出

SELECT CONVERT(DECIMAL(20,10),2*10*1.06/1000000) AS V
UNION ALL
SELECT CONVERT(DECIMAL(20,10),3*12*1.06/1000000) AS V
UNION ALL
SELECT CONVERT(DECIMAL(20,10),15*8*2.78/1000000) AS V

计算结果

                                      V
---------------------------------------
                           0.0000212000
                           0.0000381600
                           0.0003336000

[shoppo0505]

引用 1 楼 Tiger_Zhao 的回复:

DECLARE @sql varchar(max)

SET @sql = ''

SELECT @sql = @sql + '
UNION ALL
SELECT CONVERT(DECIMAL(20,10),'+CONVERT(varchar(11),ZHI)+SEC+') AS V'
  FROM #TB

SET @sql = STUFF(@sql,1,13,'')

PRINT @sql

EXEC(@sql)

PRINT输出

SELECT CONVERT(DECIMAL(20,10),2*10*1.06/1000000) AS V
UNION ALL
SELECT CONVERT(DECIMAL(20,10),3*12*1.06/1000000) AS V
UNION ALL
SELECT CONVERT(DECIMAL(20,10),15*8*2.78/1000000) AS V

计算结果

                                      V
---------------------------------------
                           0.0000212000
                           0.0000381600
                           0.0003336000

+1，高手！！！ 能不能用函数计算单行值，再全部输出？ 如果可以，函数如何写？我在函数中exec @sql 语句，然后作为varchar返回这步卡住了。

[Freeid_shi]

非常感谢，高手

[Tiger_Zhao]

DECLARE @t TABLE(v DECIMAL(20,10))
INSERT INTO @t EXEC(@sql)

从@t中取返回值