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.

Enable master..xp_cmdshell extended stored procedure of SQL Server

master..xp_cmdshell used to execute windows command like cmd/PowerShell.

Execute below commands under master database to enable xp_cmdshell extended procedure

EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

You don’t have to do anything else to enable xp_cmdshell extended stored procedure.

SQL Script to split string using SQL Server

NOTE: In 2016 version of SQL Server, a new function STRING_SPLIT (GivenString, Separator) has been implemented but for below 2016 version has no any string function like this one.

So that, I am sharing a user defined table valued function, named SplitString(GivenString, Separator, ReturnSeperator), by which you can Split any string by using any separator. Also, this function has another parameter by which you can return the separator also.

See below parameter description

SplitString(givenString , separator, returnSeperator) -- Download the sql script or see below for the coding

Returns a Table with two columns – OrdinalPosition & Value

Param Name	Type	Direction	Description
givenString	VARCHAR (8000)	IN	A String, which one you want to parse/split
separator	VARCHAR (8000)	IN	A String, which is used as a separator for concatenated strings.
returnSeperator	BIT	IN	Default is False. For True/1 value, return String will have the separator at the end For False/0, No separator will be returned.

Return Column	Type	Description
OrdinalPosition	INT	This column will represent the position of the value string in GivenString
Value	VARCHAR	String value as varchar(max length of value) which has been split from GivenString

Uses:

Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, False) Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, default)	OrdinalPosition Value 1 abc 2 xyz 3 pqr
Select * from SplitString (‘abc,xyz,pqr’ , ‘,’, true)	OrdinalPosition Value 1 abc, 2 xyz, 3 pqr,

Here is the Table Valued Function script

SplitString: download as file

-----------------------------------------------------------------------

CREATE FUNCTION [dbo].[SplitString] (   @givenString varchar(8000) , @separator varchar(100) , @returnSeparator bit = 0) RETURNS TABLE AS   RETURN (   WITH data ([start], [end])   AS   (       SELECT         0 AS [start],         CHARINDEX(@separator, @givenString) AS [end]          UNION ALL          SELECT         [end] + 1,         CHARINDEX(@separator, @givenString, [end] + 1)         FROM           data         WHERE [end] > 0   )   SELECT     ROW_NUMBER() OVER (       ORDER BY OrdinalPosition     ) OrdinalPosition,     RTRIM(LTRIM(Value)) + (       CASE         WHEN @returnSeparator = 1           THEN             @separator         ELSE           ''       END     ) Value   FROM     (       SELECT         ROW_NUMBER() OVER (         ORDER BY [start]         ) OrdinalPosition,         SUBSTRING(@givenString, [start], COALESCE(NULLIF([end], 0), LEN(@givenString) + 1) - [start]) Value         FROM           data     ) r   WHERE RTRIM(Value) <> ''     AND Value IS NOT NULL   )