Transact SQL – sp_executesql

Background

SQL Server sp_executesql is a fair middle ground between embedded SQL and programmable objects such as Stored Procedures,  Functions, and Views.

sp_executesql allows one to build out a SQL Statement yet define its parameters neatly.

Using the word neatly here to mean we are able to separate out the arguments, their datatypes, and values.

Pluses

The pluses are quite obvious:

  1. SQL Server can better codify the SQL Statement
  2. It can parse it once and recognize that it already had a plan for successive queries
  3. There is a much tighter against SQL Injections, as well

 

Function Arguments

It is pretty and straight forward function in that it requires only one arguments, the SQL Statement.

If the SQL Statement contains arguments they should be noted in the SQL Statement.  The arguments will follow same convention used for Transact SQL variable names ( @lastname, @employeeID ).

 

Code

Our example will access the WideWorldImporters database.

Simple SQL Statement


use [WideWorldImportersStandard]
go

declare @StateProvinceName sysname

declare @sqlStatement      varchar(2000)
declare @sqlParam          varchar(120)

set @StateProvinceName = 'Washington'

select
		  tblAC.CityID
		, tblAC.CityName
		, tblASP.StateProvinceID
		, tblC.CountryID
		, tblC.CountryName

from   [Application].[Cities] tblAC

inner join [Application].[StateProvinces] tblASP

	on tblAC.StateProvinceID = tblASP.StateProvinceID

inner join [Application].[Countries] tblC

	on tblASP.CountryID = tblC.CountryID

where  tblASP.[StateProvinceName] = @StateProvinceName 

Uses sp_executeSQL


use [WideWorldImportersStandard]
go

declare @StateProvinceName sysname
declare @sqlStatement      nvarchar(2000)
declare @sqlParam          nvarchar(240)

set @StateProvinceName = 'Washington'

set @sqlStatement =
		'
				select
				  tblAC.CityID
				, tblAC.CityName
				, tblASP.StateProvinceID
				, tblC.CountryID
				, tblC.CountryName

		from   [Application].[Cities] tblAC

		inner join [Application].[StateProvinces] tblASP

			on tblAC.StateProvinceID = tblASP.StateProvinceID

		inner join [Application].[Countries] tblC

			on tblASP.CountryID = tblC.CountryID

		where  tblASP.[StateProvinceName] = @stateProvinceName 

		'

set @sqlParam = '@stateProvinceName nvarchar(50)'

exec sp_executesql
		  @statement = @sqlStatement
		, @params = @sqlParam
		, @stateProvinceName = @stateProvinceName

Summary

Yes, I agree that the sample that uses sp_executesql is a lot more code and plumbing.

And, it still passes the same “big” SQL Statement over the wire.

The major difference is the clean separation between code, parameter list, and parameter values.

The advantages of programmable objects such as Stored Procedures and Functions are :

  1. The code is in one place
    • Much higher re-usability
      • Other tools and applications can utilize the stored code
      • Much better optimization choices for the engine

And, in the code of actual prepared statements as in JDBC and .Net ( SqlCommand.Prepare, etc), there advantages are:

  1. Code is not passed repeatedly
    • The code is only sent from the client to the engine once
    • Thereafter the only thing sent over are argument values

Again, in the case of sp_executesql the code is passed during each iteration.

References

  1. Microsoft
    • Docs \ SQL \ Relational databases \ System stored procedures
      • sp_executesql
        Link
    • Microsoft – Developer Network
      • System.Data.SqlClient > SqlCommand Class > SqlCommand Methods
        Link
  2. Tutorial Points
    • JDBC – Statements, PreparedStatement and CallableStatement
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s