Add a column in SQL with a special character?

Question

I know it's bad practice but the current situation requires a special character (sharp s = ß) as a column name. How can I write a SQL query which adds the column with the special character? With SSMS it adds Straße but when I run the SQL query with sqlcmd.exe through an external program it adds Straße.

This is the script:

DECLARE @Street varchar(50)='Straße';  
IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'@Street'
          AND Object_ID = Object_ID(N'Document'))
BEGIN
    EXECUTE
        (
        'ALTER TABLE dbo.Document ADD ' +
           @Street + ' varchar(50) NULL 
        ')
END

Answer 1

You need to use nvarchar not varchar:

DECLARE @Street sysname = N'Straße'; --synonym for nvarchar(128) NOT NULL
IF NOT EXISTS(SELECT 1 FROM sys.columns 
              WHERE Name = @Street --This shouldn't be a literal string
                AND Object_ID = Object_ID(N'Document'))
BEGIN
    DECLARE @SQL nvarchar(MAX);

    SET @SQL - N'ALTER TABLE dbo.Document ADD ' QUOTENAME@(Street) + N' varchar(50) NULL;'; 
    --Should the column be an nvarchar too, considering it's name?

    EXEC sys.sp_executesql @SQL;

END;