The name is not a valid identifier (dynamic SQL)

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'

CREATE TABLE #TempTableDynamicSQLIn

(

       ID INT NOT NULL,

       SomeString NVARCHAR(50) NULL

)

INSERT INTO #TempTableDynamicSQLIn

       (      ID, SomeString)

       VALUES

       (      1, ''Some String'')

SELECT * FROM #TempTableDynamicSQLIn

';

EXECUTE sp_executesql @SQL;

EXEC @SQL;

Error message:

Msg 203, Level 16, State 2, Line 16

The name '

CREATE TABLE #TempTableDynamicSQLIn

(

       ID INT NOT NULL,

       SomeString NVARCHAR(50) NULL

)

INSERT INTO #TempTableDynamicSQLIn

       (      ID, SomeString)

       VALUES

       (      1, 'Some String')

SELECT * FROM #TempTableDynamicSQLIn

' is not a valid identifier.

Solution:

EXECUTE sp_executesql @SQL;

Temp tables created in EXEC can't use temp table caching mechanism.

Run dynamic sql from the linked server to the current one

SET XACT_ABORT ON; -- In order to enable the distributed transactions

BEGIN DISTRIBUTED TRANSACTION

DECLARE @SQL nvarchar(max)

BEGIN TRY

       set @SQL = 'REVERT; EXECUTE AS LOGIN = ''SA''

              EXECUTE(''

       UPDATE / INSERT / .....

       FROM [LinkedServerDataBaseName].[LinkedServerSchemaName].[LinkedServerTableName] b

       JOIN [MyServerNameAsLinkedServerName].[DataBaseName].[SchemaName].[TableName] ns  ON b.BRN_ID = ns.BRN_ID

       WHERE .......

              '') AT [LinkedServerName]';

       exec sp_executesql @SQL

       COMMIT TRANSACTION

END TRY

BEGIN CATCH

       -- ROLLBACK TRANSACTION

       -- do something....

END CATCH

SET XACT_ABORT OFF;

LinkedServerDataBaseName - database name in the linked server

MyServerNameAsLinkedServerName - my server has to be declared as linked server in the other server, this is it's linked server name there.

LinkedServerName - the linked server name in my server

exec query on other database

DECLARE @statement NVARCHAR(MAX) = N'select * from dbo.MyTable;

DECLARE @sql NVARCHAR(MAX) = QUOTENAME('OtherDbName') + '.sys.sp_executesql';

EXEC @sql @statement;

Incorrect syntax near the keyword 'SCHEMA'

If you try to execute:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'NewSchemaName')

       CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]

GO

You will get this error message:

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'SCHEMA'.

And it although this command will success:

CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]

So why we’ve got the error?

Because schema creation must be the first command in a batch.

A solution:
Run it as dynamic SQL:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'NewSchemaName')

       EXEC sp_executesql N'CREATE SCHEMA [NewSchemaName] AUTHORIZATION [dbo]'

GO

Join to Dynamic SQL code

The situation: 

You have a dynamic SQL code that return a select results, and you want to make it available to join other queries (you don't know what will be the queries).

Solution:

1. Insert the dynamic SQL code into stored procedure.

2. Join it using OpenRowSet:

Select *

From OpenRowSet( 'SQLNCLI',

'Server=(Local);Trusted_Connection=Yes',

'Set FmtOnly Off Exec DB_Name.dbo.SP_Name'

) d

JOIN ...

Notes:

1.You need to configure the server 'Ad Hoc Distributed Queries' property to 1:

SP_Configure 'Ad Hoc Distributed Queries',1;

Go

Reconfigure With Override;

Go

2. Stored procedure that executed from OpenRowSet can't get parameters.

3. Check performance.

Error message when execute USE DB

If DB name include special characters (like '-', '.'), error messages will be displayed when execute:

USE Ddddd.7 

--> error msg: Incorrect syntax near '.7'.

The problem is in the DB name.

Solution:

USE [Ddddd.7]

It can be more dangerous when execute it in dynamic SQL:

DECLARE @DBName nvarchar(128) = DB_NAME()

DECLARE @Sql nvarchar(500)

SET @Sql = 'USE ' + @DBName + ........

EXEC (@SQL)

--> error msg: Incorrect syntax near '.7'.

It recommended to write it in this syntax:

.....

SET @Sql = 'USE [' + @DBName + ']'  + ........

EXEC (@SQL)

Few notes about Dynamic SQL

Any USE statement in the dynamic SQL will not affect the calling stored procedure.

Temp tables created in the dynamic SQL will not be accessible from the calling procedure since they are dropped when the dynamic SQL exits. 

The block of dynamic SQL can access temp tables created by the calling procedure.

The effect of SET command in the dynamic SQL lasts for the duration of the block of dynamic SQL only and does not affect the caller.

When using stored procedures, users do not need permissions to access the tables accessed by the stored procedure. This does not apply when you use dynamic SQL!

The query plan for the stored procedure does not include the dynamic SQL!!!

The block of dynamic SQL has a query plan of its own.

The first parameter @stmt of sp_executesql is Unicode - nvarchar/ntext. varchar is not valid!

sp_executesql ERROR: Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'

Error:

EXEC sp_executesql N'SELECT @x', '@x int', @x = 2

--or:

EXEC sp_executesql 'SELECT @x', N'@x int', @x = 2

This error message will be displayed:

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1

Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Explanation:

The first and the second parameters of sp_executesql are Unicode - nvarchar/ntext (depend on the MSSQL version).

varchar is not valid!

Solutions:

--1.

EXEC sp_executesql N'SELECT @x', N'@x int', @x = 2

-- 2.

DECLARE @stmt nvarchar(max), @params nvarchar(max)

SET @stmt = 'SELECT @x' -- @stmt was declared as nvarchar, so the N is not required

SET @params = '@x int' -- @params was declared as nvarchar, so the N is not required

EXEC sp_executesql @stmt, @params, 2

Search for a text including special characters in SQL Server

1. Delimits the special character with square brackets:

Examples:

for '%' we will write:

SELECT * FROM TableName WHERE ColumnName LIKE '%[%]%'

for the text 'UPDATE [TABLENAME]' , that already contain square brackets, we will delimit the open bracket:

select o.name from ..... where text like '%UPDATE [[]TABLENAME]%'

2. Use a custom escape character:

Examples:

for '%' we will write:

SELECT * FROM TableName WHERE ColumnName LIKE '%\%%' ESCAPE '\'

and for 'UPDATE [TABLENAME]' we will write:

select o.name from ..... where text like '%\UPDATE [TABLENAME]%' ESCAPE '\'

Note: you can protect the code from SQL Injection using ESCAPE: 

http://copypastenet.blogspot.com/2010/11/protect-from-sql-injection.html

Dynamic insert statement into table variable

-- variable table declaration:

declare @TempObjects table (schema_ nvarchar(50), table_ nvarchar(250))

-- set the select statement into the string variable:

declare @SQL as nvarchar(2000)

SET @SQL = 'SELECT..........'

-- insert the string into the table:

insert into @TempObjects(schema_, table_) 

EXEC(@SQL)

Dynamic SQL

DECLARE @SQL nvarchar(4000)

SET @SQL = 'SELECT .......... '

EXEC (@SQL)

EXEC sp_executesql @SQL,  

   N' @FromTime date,  

   @OIdentity nvarchar(50),  

   @Status int,   

   @Name nvarchar(50)',  

   @FromTime = @FromPlanStrTime,  

   @OIdentity = @OIdentity,  

   @Status = @Status,    

   @Name = @Name