Use Side-Effecting Operator /DML within a User Defined Function of SQL Server

SQL server’s function doesn't support DML (Insert, Update & Delete)/EXEC () procedure. Shows “Invalid use of a side-effecting operator 'EXECUTE STRING' within a function” as execution result.

If you want to insert/ update (DML) into table data or want to execute dynamic SQL statement within a function, for that you have to use sqlcmd utility. This is a SQL Server utility to execute a query/ script file immediately. To use this, sqlcmd, utility you have to enable xp_cmdshell to pass windows command.

Impotent: Object name should be fully qualified name means with database and schema name, i.e. TEST_DB1.dbo.TEST_TABLE1

Let’s execute the below INSERT command in a function

INSERT INTO [TEST_DB1].[dbo].[TEST_TABLE1] (NAME) VALUES ('TEST_NAME')

Here is the function to execute DML

CREATE FUNCTION dbo.EXECUTE_DML_IN_FUNCTION ()

RETURNS BIT

AS

BEGIN

                DECLARE @cmd VARCHAR(8000)

                DECLARE @tSql VARCHAR(8000) = 'INSERT INTO [TEST_DB1].[dbo].[TEST_TABLE1] (NAME) VALUES (''TEST_NAME'')'

                SELECT @cmd = 'sqlcmd -S ' + @@servername + ' -U TEST_USER -P TEST_PASS ' + ' -Q "' + @tSql + '"'

                EXEC master..xp_cmdshell @cmd

                                , 'no_output'

                RETURN 'True'

END

Now call the function and a record will be inserted on the table [TEST_DB1].[dbo].[TEST_TABLE1]

SELECT dbo.EXECUTE_DML_IN_FUNCTION ()

NOTE: if you want to use Windows Authentication to connect to the server, ignore -U & -P options, use -E instead.