How to Use sp_executesql stored procedure in SQL

From SQL server help document
It helps you optimize the query speed!

RecommendedSp_executesqlInstead of using the EXECUTE statement to EXECUTE a string. Support Parameter replacement not onlySp_executesqlMore common than EXECUTE, and also makeSp_executesqlIt is more effective because the execution plan generated by SQL Server is more likely to be reused by SQL Server.

Self-contained batch processing

Sp_executesqlOr when the EXECUTE statement executes a string, the string is executed as its self-contained batch. SQL Server compiles statements in a Transact-SQL statement or string into an execution plan, which is independentSp_executesqlOr EXECUTE statement. The following rules apply to self-contained batch processing:

• Until executionSp_executesqlOr EXECUTE statementSp_executesqlOr compile the statements in the EXECUTE string into the execution plan. When the string is executed, the analysis or check for its errors starts. The name referenced in the string is parsed only during execution.
• The Transact-SQL statement in the executed string cannot be accessed.Sp_executesqlOr any variable declared in the batch where the EXECUTE statement is located. IncludeSp_executesqlOr the batch processing of the EXECUTE statement cannot access the variable or local cursor defined in the executed string.
• If the execution string has the USE statement for changing the database context, the changes to the database context only continueSp_executesqlOr EXECUTE statement.

The following two batches are used as examples:

/* Show not having access to variables from the calling batch. */DECLARE @CharVariable CHAR(3)SET @CharVariable = 'abc'/* sp_executesql fails because @CharVariable has gone out of scope. */sp_executesql N'PRINT @CharVariable'GO/* Show database context resetting after sp_executesql completes. */USE pubsGOsp_executesql N'USE Northwind'GO/* This statement fails because the database context  has now returned to pubs. */SELECT * FROM ShippersGO

Replace parameter values

Sp_executesqlYou can replace the parameter values of any parameter specified in the Transact-SQL string. However, the EXECUTE statement does not. ThereforeSp_executesqlThe generated Transact-SQL string is more similar than that generated by the EXECUTE statement. The SQL Server Query Optimizer maySp_executesqlThe Transact-SQL statement of matches the execution plan of the previously executed statement to save the overhead of compiling the new execution plan.

When using an EXECUTE statement, you must convert all parameter values to characters or Unicode and make them part of a Transact-SQL string:

DECLARE @IntVariable INTDECLARE @SQLString NVARCHAR(500)/* Build and execute a string with one parameter value. */SET @IntVariable = 35SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +         CAST(@IntVariable AS NVARCHAR(10))EXEC(@SQLString)/* Build and execute a string with a second parameter value. */SET @IntVariable = 201SET @SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = ' +         CAST(@IntVariable AS NVARCHAR(10))EXEC(@SQLString)

If the statement is executed repeatedly, a new Transact-SQL string must be generated during each execution even if the value provided by the parameter is different. In this way, additional overhead is generated in the following aspects:

• The SQL Server query optimizer can match the new Transact-SQL string with the existing execution plan, which is hindered by the changing parameter values in the string text, especially in complex Transact-SQL statements.
• The entire string must be regenerated during each execution.
• During each execution, the parameter value (not a character or Unicode value) must be projected into character or Unicode format.

Sp_executesqlYou can set parameter values that are independent of the Transact-SQL string:

DECLARE @IntVariable INTDECLARE @SQLString NVARCHAR(500)DECLARE @ParmDefinition NVARCHAR(500)/* Build the SQL string once. */SET @SQLString =   N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'/* Specify the parameter format once. */SET @ParmDefinition = N'@level tinyint'/* Execute the string with the first parameter value. */SET @IntVariable = 35EXECUTE sp_executesql @SQLString, @ParmDefinition,           @level = @IntVariable/* Execute the same string with the second parameter value. */SET @IntVariable = 32EXECUTE sp_executesql @SQLString, @ParmDefinition,           @level = @IntVariable

ThisSp_executesqlThe tasks completed in the example are the same as those completed in the previous EXECUTE example, but they have the following additional advantages:

• Because the actual text of the Transact-SQL statement has not changed between two executions, therefore, the query optimizer should be able to match the Transact-SQL statement in the second execution with the execution plan generated during the first execution. In this way, SQL Server does not have to compile the second statement.
• A Transact-SQL string is generated only once.
• The integer parameter is specified in its own format. Conversion to Unicode is not required.

DescriptionIn order for SQL Server to re-use the execution plan, the object name in the statement string must fully comply with the requirements.

Reuse execution plan

In earlier versions of SQL Server, the only way to reuse the execution plan is to define the Transact-SQL statement as a stored procedure and then let the application execute the stored procedure. This generates additional overhead for managing applications. UseSp_executesqlThis helps reduce this overhead and allow SQL Server to reuse the execution plan. You can useSp_executesqlTo replace the stored procedure. Because the Transact-SQL statement remains unchanged and only the parameter value changes, the SQL Server Query Optimizer may reuse the execution plan generated during the first execution.

In the following example, the dbcc checkdb statement is generated and executed for each database except the four system databases on the server:

USE masterGOSET NOCOUNT ONGODECLARE AllDatabases CURSOR FORSELECT name FROM sysdatabases WHERE dbid > 4OPEN AllDatabasesDECLARE @DBNameVar NVARCHAR(128)DECLARE @Statement NVARCHAR(300)FETCH NEXT FROM AllDatabases INTO @DBNameVarWHILE (@@FETCH_STATUS = 0)BEGIN  PRINT N'CHECKING DATABASE ' + @DBNameVar  SET @Statement = N'USE ' + @DBNameVar + CHAR(13)   + N'DBCC CHECKDB (' + @DBNameVar + N')'  EXEC sp_executesql @Statement  PRINT CHAR(13) + CHAR(13)  FETCH NEXT FROM AllDatabases INTO @DBNameVarENDCLOSE AllDatabasesDEALLOCATE AllDatabasesGOSET NOCOUNT OFFGO

The SQL Server ODBC driver usesSp_executesqlCompleteSQLExecDirect. But the exception is:Sp_executesqlIt is not used for data parameters in execution. This allows applications that use standard ODBC functions or APIs defined on ODBC (such as RDO) to useSp_executesqlBenefits. The existing ODBC applications located in SQL Server 2000 can automatically obtain performance gains without rewriting. For more information, see use statement parameters.

The Microsoft ole db provider for SQL Server also usesSp_executesqlDirectly execute the statement with the binding parameter. Applications using ole db or ADO can be obtained without rewriting.Sp_executesqlBenefits.

1. Execute a combined SQL statement with output parameters

Declare @ Dsql nvarchar (1000 ),
@ Name varchar (50 ),
@ TablePrimary varchar (50 ),
@ TableName varchar (50 ),
@ ASC int

Set @ TablePrimary = 'id ';
Set @ TableName = 'fine ';
Set @ ASC = 1;

Set @ Dsql = n' select @ Name = '+ @ TablePrimary + N' from' + @ TableName + N' order by' + @ TablePrimary + (case @ ASC when '1' then N 'desc' ELSE n' ASC 'end)
Print @ Dsql

Set Rowcount 7
Exec sp_executesql @ Dsql, n' @ Name varchar (50) output', @ Name output
Print @ Name
Set Rowcount 0

2. Execute the combined SQL statement with input parameters

DECLARE @IntVariable INTDECLARE @SQLString NVARCHAR(500)DECLARE @ParmDefinition NVARCHAR(500)/* Build the SQL string once. */SET @SQLString =   N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'/* Specify the parameter format once. */SET @ParmDefinition = N'@level tinyint'/* Execute the string with the first parameter value. */SET @IntVariable = 35EXECUTE sp_executesql @SQLString, @ParmDefinition,           @level = @IntVariable/* Execute the same string with the second parameter value. */SET @IntVariable = 32EXECUTE sp_executesql @SQLString, @ParmDefinition,           @level = @IntVariable