Tricks on how to execute string inside a function in Sql Server -

procedure functionx, line 345

invalid use of side-effecting operator 'execute string' within function.

i above error when execute dynamic statement inside function in sql server 2012. there workaround this? tricks?

ps: sproc (stored procedure) lengthy body inserted as-is inside function.

declare @execsql nvarchar(2000) set @execsql = 'insert @table1 exec sproc1 ' + @id_company + ',' + @id_country  exec (@execsql) 

many in advance.

also, need able delete inside function well. know contradicts definition of functions wondering if there tricks can used

no there no tricks, see the curse , blessings of dynamic sql

dynamic sql in user-defined functions

this simple: cannot use dynamic sql used-defined functions written in t-sql. because not permitted in udf change database state (as udf may invoked part of query). since can dynamic sql, including updates, obvious why dynamic sql not permitted.

i've seen more 1 post on newsgroups people have been banging head against this. if want use dynamic sql in udf, out , redo design. have hit roadblock, , in sql 2000 there no way out.

in sql 2005 , later, implement function clr function. recall data access clr dynamic sql. (you safe-guarded, if perform update operation function, caught.) word of warning though: data access scalar udfs can give performance problems. if say

select ... tbl dbo.myudf(somecol) = @value

and myudf performs data access, have more or less created hidden cursor.