Showing posts with label SQL Function to Excure expression from string. Show all posts
Showing posts with label SQL Function to Excure expression from string. Show all posts

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.
----