Tricks on how to execute string inside a function in Sql Server
Procedure FunctionX, Line 345
Invalid use of a side-effecting operator ‘EXECUTE STRING’ within a
SQL run from Excel cannot use a temporary table Inserting multiple rows in a single SQL query? How do I transfer encrypted stored procedures from online DB to local? SQL Server 2008 compare two tables in same database and get column is changed How to Create Dynamic Columns in SSRS Report how to set a specific time interval for different work shifts to retrieve data
I get the above error when I execute a dynamic statement inside a function in SQL Server 2012.
Is there a workaround for this? Any tricks?
PS: The sproc (stored procedure) is much too lengthy for its body to be inserted as-is inside the function.
DECLARE @execsql NVARCHAR(2000) Set @execsql = 'INSERT INTO @TABLE1 EXEC SPROC1 ' + @ID_COMPANY + ',' + @ID_COUNTRY exec (@execsql)
Many thanks in advance.
Also, I need to be able to delete inside the function as well. I know this contradicts the definition of functions but I am wondering if there are some tricks that can be used
One Solution collect form web for “Tricks on how to execute string inside a function in Sql Server”
No there is no tricks, see The Curse and Blessings of Dynamic SQL
Dynamic SQL in User-Defined Functions
This very simple: you cannot use dynamic SQL from used-defined
functions written in T-SQL. This is because you are not permitted do
anything in a UDF that could change the database state (as the UDF may
be invoked as part of a query). Since you can do anything from dynamic
SQL, including updates, it is obvious why dynamic SQL is not
I’ve seen more than one post on the newsgroups where people have been
banging their head against this. But if you want to use dynamic SQL in
a UDF, back out and redo your design. You have hit a roadblock, and in
SQL 2000 there is no way out.
In SQL 2005 and later, you could implement your function as a CLR
function. Recall that all data access from the CLR is dynamic SQL.
(You are safe-guarded, so that if you perform an update operation from
your function, you will get caught.) A word of warning though: data
access from scalar UDFs can often give performance problems. If you
SELECT … FROM tbl WHERE dbo.MyUdf(somecol) = @value
and MyUdf performs data access, you have more or less created a hidden