using EXEC to execute a formula stored in a string

using EXEC to execute a formula stored in a string

• mnTeddy

  Thanks for the reply, but you didn't actually address the real problem.
  
  The real problem is that I need to be able to call similar code from inside a UDF.  Please look at the original post to see the explanation of the problem.
  

• JohnGreenan

  Here is the answer to my own question:
  
  After I explained the difficulty I have had with this to my users, they decided that it wasn't something that had to be done.
  
  So now it is a problem for another day.  Thanks anyway, Clifford.
  

• socalmp

  BTW, others reasons that dynamic SQL is not allowed inside functions include:
  
  i) We cannot tell whether the code would have side effects or not (side effects are not allowed in functions)
  
  ii) We cannot tell whether the code is deterministic or not (a function must be deterministic to be used in a computed column, an index, or an indexed view)
  
  Regards,
  Clifford Dibble
  
  

• Shawn Weitzel

  Hi,
  
  You wrote "Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's."
  
  Could you do this in a few passes
  
  Pass 1 - UDF selects formula templates and creates parameterized formula instances.  Use this UDF as the rowset source to fill up a temp table.
  
  Pass 2 - Iterate over temp table using a cursor calling sp_execsql on each formula expression.  Compute result and store into result column.
  
  Pass 3 - Select results from temp table
  
  Unfortunately, I don't see a nicer way to do this.  I looked at using
  
  SELECT * FROM   OPENQUERY(LOOPBACK_SERVER, @vcFormula)
  
  but apparently OPENQUERY only accepts string literals for the 2nd parameter.
  
  Regards,
  Clifford Dibble
  
  
  
  
  
  

• John Neighbors

  hello..
  
  i made test, these methods could be used...
  =================================
  declare @v1 nvarchar(10)
  declare @v2 nvarchar(10)
  declare @v3 nvarchar(10)
  set @v1 = '2.5'
  set @v2 = '4.5'
  set @v3= '5.5'

  declare @sql nvarchar(200)
  declare @param nvarchar(100)
  declare @resultOut nvarchar(100)

  -- Method 1:
  set @sql = N'select (' + @v1 + '+' + @v2 + '/' + @v3 + ')'
  exec sp_executesql @sql

  -- Method 2:
  set @sql = N'select @calc=(' + @v1 + '+' + @v2 + '/' + @v3 + ')'
  set @param = N'@calc nvarchar(30) output';

  exec sp_executesql @sql, @param, @calc=@resultOut output;
  select @resultOut
  =================================

  
  
  

• Pete Smith

  OK.  
   
  I'm truly sorry this was so painful for you.   As we begin to plan for the next post-Yukon release of SQL Server, maybe this is something we can address.  It would help to know as much about your usage scenario as possible (e.g., why you absolutely HAD to eval the formulata from inside a UDF).   As I spoke with your problem with some of my colleagues, we weren't entirely sure about all the details of your scenario. 
  
  For example, one idea that came up was just to dynamically create the entire function ("CREATE FUNCTION ...") and then drop it when you're done.  However, I argued that wouldn't work because I assumed you needed to eval one formula for each row in a result set.
  
  In any case, if you get some time, tell us about your scenario and restrictions and so on.
  
  Thanks,
  Clifford Dibble
  

  
  
  

• snaill

  Clifford,
  
  First off, thanks for being so eager to help.  I think you should be commended for your genuine desire to help out the folks on this board.  Secondly, I really appreciate Microsoft for setting up these forums - they have already been a great help to me, and I'm sure others as well.
  
  Basically, we have a huge legacy materials accounting program that was written in PowerBuilder.  I have been given the task to rewrite a major piece of this program in TSQL.  The piece in question is a globally defined function that is very complex.  Its purpose is to return several pieces of data about individual quantities of some of our materials, given a few parameters to specify a particular individual quantity.  Some of the returned data is simply mined from the database, the rest of it is calculated.  The TSQL function I have written returns a table that contains all the desired values.
  
  Sometimes this TSQL function will be called by what PowerBuilder calls "inline SQL", which is SQL that is compiled and executed like regular PowerBuilder function calls.  At other times, this function will be called as the basis of a "datawindow" (i.e. a structure fairly similar to an MFC CRecordSet).  Finally, we wanted our users to be able to call the function and join to the table that it returns, in a larger query.  Although this final restriction seems difficult right now, I refer to your answer in another thread about how this may be done in the future using CROSS APPLY.  It is because of these reasons that a SP seemed to not be the correct choice for our needs, although in hindsight it now seems like one may have worked at least as well as a UDF.
  
  You are correct in your assumption that each row in the set may have a different formula to evaluate.
  
  If you have any specific questions, please feel free to ask.
  
  Dean
  
• using EXEC to execute a formula stored in a string