create function f_calc( @str varchar(1000)--要计算的表达式 )returns sql_variant as begin declare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255) declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out if @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript' if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@str if @err=0 return(@re)lb_err: exec sp_oageterrorinfo NULL, @src out, @desc out declare @errb varbinary(4),@s varchar(20) set @errb=cast(@err as varbinary(4)) exec master..xp_varbintohexstr @errb,@s out return('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc) end go select dbo.f_calc('5*6*7')
SELECT CONVERT(INT, 5*6*7 ) select cast(5*6*7 as int)加上引号后表示字符串了
字符串不可以转换为INT (除了是纯数字)
SELECT COM_COD,STANDARD,exec('SELECT'+SUBSTRING(STANDARD,1,LEN(STANDARD)-2)))*2 FROM PC_SUP_MAT WHERE COM_COD LIKE '124%' 這樣講為什麼不行 一定要先定義嗎,可不可以寫在一條SQL語句中
create function f_calc(
@str varchar(1000)--要计算的表达式
)returns sql_variant
as
begin
declare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255)
declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out
if @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript'
if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@str
if @err=0 return(@re)lb_err:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4),@s varchar(20)
set @errb=cast(@err as varbinary(4))
exec master..xp_varbintohexstr @errb,@s out
return('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc)
end
go
select dbo.f_calc('5*6*7')
---------------------------
210(所影响的行数为 1 行)
set @s='5*6*7'
exec('select '+@s)
/*
-----------
210(1 個資料列受到影響)*/
SELCT CONERT(INT,SUBSTRING(字段1,1,LEN(字段1)-2))
字段1='5*6*7MM'
SELECT CONVERT(INT, 5*6*7 )
select cast(5*6*7 as int)加上引号后表示字符串了
FROM PC_SUP_MAT
WHERE COM_COD LIKE '124%'
這樣講為什麼不行
一定要先定義嗎,可不可以寫在一條SQL語句中