Thursday, January 13, 2011

MS SQL Function Execute Expression from String

USE master
Create table EvalRes (expVal real)
go

USE master
Create Function dbo.Eval (@exp varchar(5000))
returns real
as
begin

declare @ans real
declare @osqlStmt varchar(5000)

set @osqlStmt = 'osql.exe -E -q' + '" Insert master..EvalRes select ' + @exp + '"'
exec master..xp_cmdShell @osqlStmt
select @ans = expVal From EvalRes
set @osqlStmt = 'osql.exe -E -q' + '" delete master..EvalRes ' + '"'
exec master..xp_cmdShell @osqlStmt
return @ans
end
go

---example call : select master.dbo.Eval ('((4.5)+5)*6-1-(3*7)+3.512')

If you have any problem in Execute the above script then run the following scripts:

--To enable xp_cmdshell extended stored procedure, you can run the below code as the sa account or another administrator account;
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
-- Returns the following message after a successfull execution
-- Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
-- Returns the following message after a successfull execution
-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
-- Returns the following message after a successfull execution
-- Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
----

No comments:

Post a Comment