Fun little UDF puzzle
-
This is a simple version of a real-world problem I'm trying to solve. The basic idea is to pass a numeric expression into a UDF and return the result of evaluating the expression. So if I pass in '1+2', I would expect to receive a '3' as a result. Here is a simple test harness:
drop function MyFunction
GO
create function MyFunction (@SomeString VARCHAR(100)) RETURNS FLOAT
AS
BEGIN
declare @Result FLOAT,
@QueryString VARCHAR(100)
-- What goes between these comment lines
-- so that @result = the arithmetic result of the string?????
SET @QueryString = 'SELECT @Result = ' + @SomeString
exec(@QueryString)
-- End of comment lines
return @Result
end
GO
-- test code begins here
declare @MyString VARCHAR(100)
set @MyString = '1 + 2 + 3' -- should result in '6'
SELECT dbo.MyFunction (@MyString)
set @MyString = '(99 / 3) + 22' -- should result in '55'
SELECT dbo.MyFunction (@MyString)
-- end of test code
Any ideas?
Wayne
-
Hmmmmm. Not much fun with functions! Here's a solution in a stored procedure. Will do the job in some cases, but WHERE is the val() function in SQL? It's been around in BASIC since the stone age!
create proc eval @mystring varchar(200)
as
set nocount on
declare @result float
create table ##evalresult (
result float
)
select @mystring = 'insert ##evalresult select ' + @mystring
exec (@mystring)
select @result = result from ##evalresult
drop table ##evalresult
return @result
--test code
declare @myres float
exec @myres = eval '1 + 2 + 3'
select @myres
-
Thanks for the response. I have the system implemented using a stored proc right now. My intention is to move the logic into a UDF so that I can then use it in a computed column, and in views. Looks like I am stuck with the stored proc approach for the moment.
Thanks for the response, and have a great day!
Wayne
-
This looks like it works but is very basic with no error handling. I have not fully tested it but am happy with it
CREATE FUNCTION CalcIt (@calc as nvarchar (255))
RETURNS float AS
BEGIN
Declare @res as float
set @res = @calc
return @res
END
Sample use
select dbo.calcit((1+2)*12+year(getdate()))
Hope it helps
-
The code posted at the bottom of this post shows what I have tried so far. Each line that failed has the error message as an end-of-line comment. From books online, UDFs do not support dynamic SQL. This eliminates most of the obvious solutions. CalcIt works well when used interactively and passed a numeric expression. However, when passed a string containing that same numeric expression calcit generates the error message "Error converting data type nvarchar to float". One of the problem constraints is for the UDF to accept a string variable.
DECLARE @TESTSTRING VARCHAR(50)
SET @TESTSTRING = '(1+2)*12+year(getdate())'
select dbo.calcit(@TESTSTRING)
-- generates a runtime error message
I am currently working on a set of primitive functions and my own recursive expression parser very similar to the calculator we all wrote in college in computer algorithms 201. Similar solutions exist on various SQL sites for working with imaginary numbers and such. Seems to be the normal workaround. It is rather annoying that something as simple as evaluating an expression should require this much effort. (Is anyone at Microsoft listening? Please?)
Thanks all for your ideas, and have a great day!
Wayne
Tested (and failed) attempts so far:
DROP function MyFunction
GO
create function MyFunction (@InSomeExpression VARCHAR(100))
RETURNS FLOAT AS
BEGIN
DECLARE @thisResult FLOAT,
@QueryString1 VARCHAR(500),
@QueryString2 VARCHAR(500)
DECLARE @testTable TABLE (Result NUMERIC (16,6))
SET @QueryString1 = 'SELECT @thisResult = ' + @InSomeExpression
-- EXECUTE sp_EXECUTESQL @QueryString1 -- Only functions and extended stored procedures can be executed from within a function.
-- EXECUTE @QueryString1 -- Could not find stored procedure 'SELECT @thisResult = 1+2'.
-- EXECUTE (@QueryString1) -- Invalid use of 'EXECUTE' within a function.
SET @QueryString2 = 'INSERT INTO @testTable(result) SELECT ' + @InSomeExpression
-- EXECUTE (@QueryString2) -- Invalid use of 'EXECUTE' within a function.
-- SET @thisResult = @InSomeExpression -- Error converting data type varchar to float.
RETURN @thisResult
END
GO
DECLARE @TESTSTRING VARCHAR(50)
SET @TESTSTRING = '1+2'
SELECT dbo.MyFunction (@TestString)
-
G'day all,
Thanks for all of the suggestions. Everyone confirmed that there was no easy way to achieve what I was trying with the current versions of Sql Server. Once we established that fact, the answer was clear. I simply wrote an eval function similar in concept to the VB eval function using nothing but SQL. The result is two script packages that are now loaded out to the library here on the site.
The first script is a simple stack engine using a string to simulate a stack of single characters. This is used to maintain a stack of the operators in the expression. The script contains the searchable word "SqlStackEngine" at the top.
The second script is the actual calculation engine and currently supports the four basic arithmetic operators, +-/*, as well as nested parenthetic expressions. The script contains the searchable "SQLCalcEngine" at the top.
Both scripts contain a unit test harness in-line but commented out. Be warned that there is a small performance hit for queroes that use these packages, particularly if a computed column is based on an expression.
The next enhancement will be to implement two-character operators, as well as support the common SQL functions in the expressions.
Thanks for the earlier assistance.
G'day
Wayne
-
What about sp_ExecuteSql.
It can pass values BACK out of dynamic statement by binding variables inside the dynamic statement to variables in the calling context. Something like:
declare @parm1 integer
set @parm1 = 0
declare @sqlstm nvarchar(200), @parmlist nvarchar(200)
set @sqlstm = 'set @parm1 = 1+2+3*100'
set @parmlist = '@parm1 integer OUTPUT'
exec sp_ExecuteSql @sqlstm, @parmlist, @parm1 OUTPUT
print @parm1
You would add the 'set @parm1 =' bit inside the procedure.
-
Great idea. However it was tried and failed.
The problem constraints are that the solution needs to be implemnted as a UDF, and has to accept an expression such as 1+2*3 as a string. Atttmpting to use "EXECUTE sp_EXECUTESQL" inside of a UDF results in this error message: "Only functions and extended stored procedures can be executed from within a function."
G'day
Wayne
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply