steve.laforge (11/1/2012)
I thought you had it until I tried to add it to a function. Then I get the error 'Invalid use of a side-effection operator 'INSERT EXEC' within a function.'.I need to be able to use the solution in a function. The idea is that I have built a mathematical string during a query that I actually need to execute and get the results.
Thank you!
Yeah, INSERT...EXEC has some restrictions, sorry.
You could go back to trying with a SQLCLR object and just issue the SELECT <math> on the built-in connection back to SQL Server. I doubt it would scale well, but it would not qualify as an INSERT...EXEC and should give you the desired behavior. I do not have time now to test it, but may give it a whirl later.
edit: looks like we were typing at the same time an that you found a good way forward, thanks for posting a solution
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato