Using sp_executeSQL with Variables
Published: Feb 26, 2016
When looking through other people’s code I tend to find that people struggle with dynamic SQL and how to write it in the best way. Specifically when it comes to using variables within the dynamic string.

Generally people will start to concatenate all over the place, which tends to involve awkward conversions or casts and can start to look really messy and unreadable.

However, there is a way to get around these problems by using sp_executeSQL as this allows you to simply pass through your variables into the dynamic code and saves a lot of heartache in the process.

I’ll show you what I mean with a few examples:

Firstly let’s take a simple piece of dynamic SQL (it’s utterly pointless, but will suffice for a demo) which uses sp_executeSQL instead of simply exec because we know that sp_executeSQL will store a parameterized plan, which we want:

use AdventureWorks2012
go

declare @salesOrderID int = 43659,
      
@sql nvarchar(max)

select @sql =
'
select *
from sales.salesOrderDetail
where salesOrderID = '
+ convert(varchar(10), @salesOrderID)

exec sp_executeSQL @sql


As mentioned, this is pretty pointless code, but it does show how even something as simple as an ID being added into a dynamic string requires a convert and already starts to look messy.

But what if we wanted even more variables? And what if one was a date?

use AdventureWorks2012
go

declare @salesOrderID int = 43659,
      
@orderQty tinyint = 1,
      
@unitPrice decimal(8, 3) = 2039.994,
      
@modifiedDate datetime = '2005-07-01 00:00:00.000',
      
@sql nvarchar(max)

select @sql =
'
select *
from sales.salesOrderDetail
where salesOrderID = '
+ convert(varchar(10), @salesOrderID) + '
and orderQty = '
+ convert(varchar(3), @orderQty) + '
and unitPrice = '
+ convert(varchar(15), @unitPrice) + '
and modifiedDate = '''
+ convert(varchar, @modifiedDate, 101) + ''''

exec sp_executeSQL @sql


Now it looks really messy and this is just a simple piece of demo code. In the real world where you’re trawling through hundreds of lines of t-SQL this type of dynamic SQL can be horrible.

So how does sp_executeSQL help? Well you can pass parameters through rather than trying to create a long string. This makes things much more readable and it’s something I’d push everyone to use where possible in order to improve readability of code for others:

use AdventureWorks2012
go

declare @salesOrderID int = 43659,
      
@orderQty tinyint = 1,
      
@unitPrice decimal(8, 3) = 2039.994,
      
@modifiedDate datetime = '2005-07-01 00:00:00.000',
      
@sql nvarchar(max)

select @sql =
'
select *
from sales.salesOrderDetail
where salesOrderID = @dynSalesOrderID
and orderQty = @dynOrderQty
and unitPrice = @dynUnitPrice
and modifiedDate = @dynModifiedDate'

exec sp_executeSQL @sql,
      
N'@dynSalesOrderID int, @dynOrderQty tinyint, @dynUnitPrice decimal(8, 3), @dynModifiedDate datetime',
      
@salesOrderID, @orderQty, @unitPrice, @modifiedDate


As you can see, you simply write the code in the dynamic string using variables, then declare those variables as the second parameter of your sp_executeSQL statement, followed by passing through a value for each variable in turn.

It might seem confusing at first, but it’s simple when you’ve got the hang of it.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron