SQL Server EXEC or SP_EXECUTESQL Commands in User Defined Functions
I developed a user-defined function named ReplaceTextColumn. I build the udf function and coded t-sql statements in order to build dynamic t-sql codes. I tried to use this dynamic sql codes to replace text or ntext sql data type columns in sql databases.
I tried to execute the resultant t-sql code block by calling the EXEC sp_executesql syntax but get the following SQL Server error message, Msg 443
Msg 443, Level 16, State 14, Procedure ReplaceTextColumn, Line 20
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function.
Also calling the user defined function in a select statement resultant with sql engine raise the following error message Msg 557
select dbo.ReplaceTextColumn('','','','','','')
--Msg 557, Level 16, State 2, Line 1
--Only functions and some extended stored procedures can be executed from within a function.
So I changed the structure to use stored procedure instead of using user defined functions (udf) because of the limitations of user functions in SQL Server.