Escape square brackets in SQL Server

Question

I read this post about putting column name and table name as variables. I want square brackets wrapping column name and table name. I saw some people do this:

@sql = 'SELECT [' + @column_name + '] FROM ...'

I tried to put square brackets into the variable like:

CREATE PROCEDURE [dbo].[find_most_frequent] 
    @table_in VARCHAR,
    @col_2 VARCHAR 
AS
BEGIN 
    DECLARE @sql NVARCHAR(4000);

    SET @sql =
--start of code
'SELECT' +
        @col_2 +
    ' FROM ' + @table_in +
' GO'
--end of code    
    print @sql
    EXEC SP_EXECUTESQL @sql
END 
GO

EXEC [dbo].[find_most_frequent]
        @table_in = '[[]dbo].[[]t1]'
        ,@col_2 = '[[]c1]' 
GO

I have used [[] to escape square brackets, as discussed here. But it does work, @sql is

SELECT[ FROM [ GO

Can somebody help please? Thanks!

score 2 · Accepted Answer · answered Aug 25 '16 at 00:51

2

varchar [ ( n | max ) ]

When n is not specified in a data definition or variable declaration statement, the default length is 1.

https://msdn.microsoft.com/en-AU/library/ms176089.aspx

answered Aug 25 '16 at 00:51

Anton

2,846
1
10
15

score 1 · Answer 2 · answered Aug 25 '16 at 03:57

Regarding the quoting of object names, there is a function in SQL Server to do exactly that: quotename().

Be careful, though, as the function has a nasty habit of returning NULL when its input exceeds 128 characters. It doesn't happen when you quote object and / or column names, but might fire up in other cases.

Escape square brackets in SQL Server

2 Answers2