sp_executesql
sp_executesql is a built-in stored procedure to execute the SQL query string. It is very effective than EXECUTE.
Instead of using the EXECUTE statement to run the SQL Query string, we can use the sp_executesql stored procedure.
sp_executesql has some major advantages over Execute statement:-
1)We can use this stored procedure to supports parameter substitution.
2)We can use this stored procedure to generates execution plans that are more possible to be reused by SQL Server
sp_executesql can be executed in the following ways:
Executing a simple SELECT statement
By using the sp_executesql statement, we can run the simple SQL statement as shown below.
The following example executes a simple SELECT statement that contains an embedded parameter named @Treelevel.
EXECUTE sp_executesql
N’SELECT * FROM tblFamilyTree WHERE TreeLevelID = @Treelevel’,N’@Treelevel. INT’, @Treelevel.= 1
Executing a dynamically built string
Very complex query can be run using the sp_executesql stored procedure. To execute the dynamically built, we can use the sp_executesql.
The following example shows using sp_executesql to execute a dynamically built string.
-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
/* Build the name of the table. */
SUBSTRING( DATENAME(mm, @OrderDate), 1, 3) +
CAST(DATEPART(yy, @OrderDate) AS CHAR(4) ) +
'Sales' +
/* Build a VALUES clause. */
' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
' @InsOrdMonth, @InsDelDate)'
/* Set the value to use for the order month because
functions are not allowed in the sp_executesql parameter
list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)
EXEC sp_executesql @InsertQueryString,
N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
@InsOrdMonth INT, @InsDelDate DATETIME',
@PrmOrderID, @PrmCustomerID, @PrmOrderDate,
@OrderMonth, @PrmDeliveryDate
Using the OUTPUT Parameter
We can use sp_executesql stored procedure to get the data from the
Query execution by using the OUTPUT Parameter.
The following example uses an OUTPUT parameter to store the result
set generated by the SELECT statement in the @ SQLQueryString
parameter.
EXECUTE sp_executesql
@SQLQueryString
,@ParmDefinition
,@CustomerID = @IntVariable
,@OrderOUT = @OrderNumber OUTPUT;
--This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
|
| Author: Mohan 28 Oct 2009 | Member Level: Diamond Points : 1 |
Good Article.
While you are using sp_executesql the declared variable @InsertQueryString should be nvarchar or nchar datatype.
otherwise it is not work.
|
| Author: Melchior 28 Oct 2009 | Member Level: Gold Points : 1 |
Yes, you are correct.
We should declare the SQL query string variable as NVarchar or NChar datatype.
|