1

I am trying to evaluate an expression in a function, and it happens that XQuery is not able to get string from the SQL variable

DECLARE @XML AS XML = ''
DECLARE @Formula AS NVARCHAR(MAX) = '1+1'
SELECT @XML.query(@Formula)

Using ...

SELECT @XML.query('sql:variable("@Formula")')

... just returns a string 1+1, and not the sum.

Is there a way to make this work in SQL (without using stored procedures, because those will not run form within the function)?

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CubicsRube
  • 71
  • 1
  • 3
  • 10
  • 1
    The way to evaluate expressions in TSQL is using `EXEC` or `sp_executesql`, but neither of those can be used within a user-defined function (see [this question](http://stackoverflow.com/questions/9850894/string-expression-to-be-evaluated-to-number)). You mentioned using a function, but it isn't clear if it's a TSQL UDF or not; if you can be more precise about how you're executing this code then someone may have a better answer. – Pondlife May 15 '13 at 19:50
  • @Podnlife Thank you for your answer. So, I guess either this should be a 1) CLR library, or 2) stored procedure, or 3) return a string and finish calculation in the application (in my case it is possible). No way to do this in the UDF. And yeah, it _is_ a T-SQL UDF :) – CubicsRube May 16 '13 at 05:41
  • 1
    Yes, those are exactly your options. A TSQL UDF [cannot modify the database state](http://msdn.microsoft.com/en-us/library/ms191320.aspx), which makes sense because most people would not want a query like `select dbo.MyFunc(SomeColumn) from dbo.SomeTable` to also modify data. – Pondlife May 16 '13 at 13:18

0 Answers0