Quotename function in SQL server The question I have is

Why use:

QUOTENAME ('simple string', '''');


Instead of:

QUOTENAME ('simple string', ' ' ');


if I want to use single quotation as the delimiter?  I assume it has to do with escaping, but can't find any detailed explanation about this function.
Two single-quotes together indicate a zero-length string.

Three single-quotes together indicate an escaped single quote; i.e. ''' becomes '

Four single-quotes together indicate an escaped single quote followed by another single quote, which is translated into a quotated single quote, similar to what you desire.  I know, it's confusing.  Try writing nested dynamic SQL, and you'll pretty quickly find strings with 7, 8, or more single quotes in a row.

Consider the output of the following three statements:

SELECT '[' + '' + ']'
SELECT '[' + '''' + ']'
SELECT '[' + '''-''' + ']'



[]

[']

['-']


Typical usage of QUOTENAME() is to protect against SQL Injection when concatenating T-SQL strings, by escaping text that is provided by a user.  For instance:

DECLARE @MyInput VARCHAR(50);
SET @MyInput = 'some table name';
DECLARE @cmd NVARCHAR(1000);
SET @cmd = 'SELECT * FROM ' + QUOTENAME(@MyInput) + ';';
PRINT (@cmd);


will output SELECT * FROM [some table name];.

If you don't use the QUOTENAME() function:

DECLARE @MyInput VARCHAR(50);
SET @MyInput = 'some table name';
DECLARE @cmd NVARCHAR(1000);
SET @cmd = 'SELECT * FROM ' + @MyInput + ';';
PRINT (@cmd);


You get SELECT * FROM some table name; which is not valid T-SQL because of the spaces in some table name.

Here comes little bobby tables:



You can see the effect of quotename in the following code:

IF OBJECT_ID('tempdb..#Students') IS NOT NULL
DROP TABLE #Students;
CREATE TABLE #Students
(
    StudentName VARCHAR(50)
);

INSERT INTO #Students (StudentName) VALUES ('Max');

SELECT *
FROM #Students;

    DECLARE @MyInput VARCHAR(50);
    SET @MyInput = 'Robert'';DROP TABLE #Students--';
    DECLARE @cmd NVARCHAR(1000);
    SET @cmd = 'SELECT * FROM #Students WHERE StudentName = ''' + QUOTENAME(@MyInput, '''') + ''';';
    PRINT (@cmd);
    EXEC (@cmd);

SELECT *
FROM #Students;

GO

    DECLARE @MyInput VARCHAR(50);
    SET @MyInput = 'Robert'';DROP TABLE #Students--';
    DECLARE @cmd NVARCHAR(1000);
    SET @cmd = 'SELECT * FROM #Students WHERE StudentName = ''' + @MyInput + ''';';
    PRINT (@cmd);
    EXEC (@cmd);

SELECT *
FROM #Students;


The output:



With the QUOTENAME() function, we do get an error message, but without the function, we allow our user to drop the #Students table.

Note, I'm using 4 single quotes in my QUOTENAME() function, since that is actually interpreted as one single quote, and used to wrap the text provided to the function, thereby making that text "safe".

Erland Sommarskog has an excellent piece on dynamic SQL at http://www.sommarskog.se/dynamic_sql.html - this is well worth reading (and understanding!) if you are considering doing dynamic SQL.