Dynamic SQL, using QUOTENAME

Use QUOTENAME for single quoting, it makes code readable and help to prevent SQL injection problems.

The function QUOTENAME have a parameter for what charachter to use for enclosing the string. The default value is [ and thats perfect for object names. One thing to note is that the parameter takes only 1 charachter but it automatically uses the equivalent other.

Samples:

QOUTENAME('A Frog', '[') --> [A Frog], QOUTENAME('A Frog', ']') --> [A Frog]

QOUTENAME('A Frog', '{') --> {A Frog}, QOUTENAME('A Frog', '}') --> {A Frog}

With the parameter you can use QOUTENAME for dynamic SQL when enclosing strings with single quotes to make it more readable and secure.

Disclaimer:You should always try to avoid what I do in the code below and not concatenate a parameter value to the execution statement string, this is for educational purposes only. Use parameters with sp_executesql instead.

CREATE OR ALTER PROC _Danger
	@CustomerName NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	SET ARITHABORT ON;

	DECLARE
		@SqlStatement NVARCHAR(MAX);

	/*This is no good*/
	SET
		@SqlStatement = 'SELECT ''' +  @CustomerName + ''' AS _txt;';

	EXEC sp_executesql @SqlStatement = @SqlStatement;
	Print @SqlStatement;

	/*Do this*/
	SET
		@SqlStatement = 'SELECT ' + QUOTENAME(@CustomerName, CHAR(39)) + ' AS _txt;';
	
	EXEC sp_executesql @SqlStatement = @SqlStatement;
	Print @SqlStatement;
END;

GO

EXEC _Danger
	@CustomerName = ''';DECLARE @s NVARCHAR(1024) = N''SELECT * FROM sys.sysusers ''; EXEC sp_executesql @s ; SELECT ''';

/*The result*/
/*
--Without QUOTENAME, this will execute and do bad stuff!!
SELECT '';DECLARE @s NVARCHAR(1024) = N'DROP_ DATABASE ' + QUOTENAME(DB_NAME()); EXEC _sp_executesql @s ; SELECT '' AS _txt;

--With QUOTENAME, this will only return a string
SELECT ''';DECLARE @s NVARCHAR(1024) = N''DROP_ DATABASE '' + QUOTENAME(DB_NAME()); EXEC _sp_executesql @s ; SELECT ''' AS _txt;
*/