sp_executesql Example with Parameters in SQL Server

The sp_executesql is a procedure in SQL Server. It offers to execute of the dynamically constructed SQL statements. Occasionally executing the constructed SQL batches dynamically is a technique used to beat out different problems in SQL programming.

For instance, when you want to signify the displayed columns in your reports, this procedure can be a solution option for you. In the simple example, this procedure takes a constructed SQL batch dynamically and other parameters. Then execute it in the runtime. Finally, it returns the result.

Now, we are going to share the sp_executesql example with parameters in SQL Server. For your information, in these examples, the sample AdventureWorks database are going to be used.

• More info: work2vec Word Vector on Python

SP_EXECUTESQL SYNTAX

The code below describes the syntax:

sp_executesql@stmt,N’@parametername1_datatype,@parametername2_datatype,@parameternameN_datatype’

,@parametername1=’Value1′ ,@parametername2=’Value2′,@parameternameN=’ValueN’

You have to remember that @stmt parameter is used to determine dynamically generated SQL statement/ batch. The data type of this parameter has to be Unicode strings. In this reason, you have to add N prefix for the direct text usage. Or you have to use nvarchar/ nchar data typed variables.

While parameter name data type specify the parameter’s name and data type which has been used in the constructed SQL statements dynamically. With the help of the parameter name expression, you will be able to set a value to the defined parameters that are placed in the SQL statement. In the following text, we are going to explore the usage details with examples.

SP_EXECUTESQL EXAMPLE

The main purpose of this example is to retrieve data from the Person table which is taking part under the same schema on the AdventureWorks database. The dynamically constructed SQL statement is going to be assigned to the @SqlStatment variable. The @ColName variable is used to determine the column names, that you want to display in the result set of the query.

As a last step, you are going to filter the Person table data with the @PerType parameter. This parameter data type is going to be nchar(2) and filter the data whose Person type column expressions equal to EM. Lastly, you are going to need to execute the query and achieve the result.

The result set of the query displays only First name, Middle name and Last name columns because of the assigned value of the @ColNames variable. Meantime, you are able to adjust the displaying column names with this parameter. For instance, the example below will be showed only first name column:

DECLARE  @SqlStatment AS NVARCHAR (1000)

DECLARE  @ColNames AS NVARCHAR (100)

SET@ColNames = N’FirstName’

SET@SqlStatment=’SELECT’+@ColNames+’FROM.Person.PersonWHERE Persontype=@PerType’  

EXECUTE sp_executesql @SqlStatment , N’@PerType nchar (2)’, @PerType=’EM’

GETTING SP_EXECUTESQL RESULT WITH OUTPUT PARAMETER

The sp_executesql return execution result of the dynamically constructed SQL statement. The output parameter plays a key role to solve this case. In this instance, you are going to count the row number of the PersonPhone table and then you are going to set the return value to a variable with the output parameter.

The tip of this usage is to indicate the @RowNumber parameter as an output parameter. Then, you assign this internal parameter value to the @Result parameter.

DECLARE  @SqlStatment AS NVARCHAR(1000)

DECLARE  @PhoneIdType AS INT

DECLARE  @Result AS INT     

SET @SqlStatment = ‘SELECT @RowNumber= COUNT(PhoneNumber) from Person.PersonPhone WHERE PhoneNumberTypeID=@PhoneType’

SET @PhoneIdType = 1

sp_executesql @SqlStatment N,’@PhoneType INT,@RowNumber INT,OUTPUT’, @PhoneType= @PhoneIdType, @RowNumber =@Result OUTPUT.

SELECT @Result AS [TableRowNumber]

SP_EXECUTESQL VS EXEC STATEMENT

The exec statement is another option to execute the SQL statements. For instance, you are able to execute the following dynamically constructed SQL statement through the EXEC statement. In the previous sample, we executed the dynamically constructed query with the EXEC statement.

However you need to take account one point regarding it. You could not parametrize the EXEC statement and this is the main drawback of it. You have to know that the sp_executesql has several advantages comparing to the EXEC statement. Now, let us see these:

• sp_executesql has the ability to reuse the cached query plans

In fact, each query executed in SQL Server is compiled before it is executed. This query compilation process results an output which is called the query plan. But, sometimes this query compilation process may be very expensive.

In this case, SQL Server want to reuse the cached query plans as possible as for the same queries in order for degrading the query compilation costs. Right now, we are going to prove this nice idea.

Firstly, we are going to clear all the cached plans with ‘freeproccache’. But, you do not execute this command in the production environment as it can be damage to the performance of the SQL Server.

Now, we are going to repeat a similar test scenario for the EXEC statement. In this step, we are going to execute the dynamically constructed query 3 times for the random parameters with the EXEC statement.

In this time, we are going to re-check sys.dm_exec_cached_plans view to know how many query plans were made. As a result, the sp_executesql produced a one query plan in the first execution of the query. Then it used the same query plan again. In spite of that, the EXEC statement made new query plans for each query execution.

• Don’t miss: work2vec Program Implementation on Python Gensim Library

This type of usage can consume SQL Server resources and can be caused by performance problems. Remember that the sp_executesql offers to generate parameterized dynamic queries. Therefore, it is more secure to SQL injection attacks.

In this page, we explained the sp_executesql procedure details and learned the usage ways. Of course, this procedure is very useful to solve the dynamic query problem. But, you need to consider the SQL injection issues once you decide to use dynamic queries in SQL Server.

For those who do not know SQL Server, simply it is a database server which implements the Structured Query Language (SQL). There are lots of versions of Microsoft SQL Server, catering for different workloads and demands. A data centre version is set up to higher levels of application support and scalability, while the Express version is a scaled down.