sp_executesql – Let's learn How to Use It


In the post SQL Server - Using SQL Agent JOBs to Scheduleand Execute Multiple Stored Procedures in Parallel, I’ve demonstrated the usage of sp_executesql and how powerful and helpful it is. This time I’ll put more explanations about it. Talk is cheap, and let’s see the code.
DECLARE @ModDate datetime = '2017-12-04 00:00:00.000'

SELECT
       [AddressID]
    ,[AddressLine1]
    ,[AddressLine2]
    ,[City]
    ,[StateProvinceID]
    ,[PostalCode]
    ,[SpatialLocation]
    ,[rowguid]
    ,[ModifiedDate]
FROM [AdventureWorks2017].[Person].[Address]
WHERE [ModifiedDate] <= @ModDate
The above code is straightforward enough. Now let’s consider the below scenarios – suppose our database name is composed by the keyword ‘AdventureWorks’ plus the year number suffix (in our example it’s 2017); when we provide a date value for the variable @ModDate, we expect the script to fetch results from the database with the same year number suffix as that of the @ModDate (e.g. if we grant the @ModDate variable with the date ‘2018-01-25’, the script should grab data from the database AdventureWorks2018). If you’re as lazy as me, you’ll probably want to build a dynamic SQL to automatically fulfill the above requirements instead of changing the database name manually.
If it’s the first time you do something like this, it would probably take you some effort before you built something as below
DECLARE @ModDate datetime = '2019-12-04 00:00:00.000'

DECLARE @ModDateYear varchar(4) = CAST(DATEPART(YEAR,@ModDate) as varchar(4))

DECLARE @DynamicSQL varchar(max) =
'SELECT
       [AddressID]
    ,[AddressLine1]
    ,[AddressLine2]
    ,[City]
    ,[StateProvinceID]
    ,[PostalCode]
    ,[SpatialLocation]
    ,[rowguid]
    ,[ModifiedDate]
FROM [AdventureWorks' + @ModDateYear + '].[Person].[Address]
WHERE [ModifiedDate] <= @ModDate'

select (@DynamicSQL)

--EXEC (@DynamicSQL)
Hmm, a careful man you’re indeed. You commented the EXEC statement because you wanted to see what you’d built first, before you could confidently execute your dynamic SQL to return the result you expected. Your carefulness had repaid you well, as the below result was returned
Oh look, isn’t this the same as the first SELECT statement at the beginning of this post? Considering the @ModDate variable has been defined, if we un-comment the EXEC statement and re-execute everything, we should get what we expected, right?
You did that quickly and sadly found that the below error message was returned


What?! You couldn’t believe what you saw. ‘The scalar variable @ModDate is defined, right at the beginning of the script!’ you shouted. Yet you quickly chilled down and started to think what the issue could be, and an idea popped out – it’s the dynamic SQL script we executed, and the @ModDate definition part is not included in it. With that in mind, you quickly modified your script by including the @ModDate definition part as below, and the script worked.
DECLARE @ModDate datetime = '2019-12-04 00:00:00.000'

DECLARE @ModDateYear varchar(4) = CAST(DATEPART(YEAR,@ModDate) as varchar(4))

DECLARE @DynamicSQL varchar(max) =
'DECLARE @ModDate datetime = ''2019-12-04 00:00:00.000''

SELECT
       [AddressID]
    ,[AddressLine1]
    ,[AddressLine2]
    ,[City]
    ,[StateProvinceID]
    ,[PostalCode]
    ,[SpatialLocation]
    ,[rowguid]
    ,[ModifiedDate]
FROM [AdventureWorks' + @ModDateYear + '].[Person].[Address]
WHERE [ModifiedDate] <= @ModDate'

--select (@DynamicSQL)

EXEC (@DynamicSQL)
Meanwhile in a paralleled universe, after you encountered the above issue, you were like ‘Let’s try to invoke the value of @ModDate directly in our dynamic SQL’, and you built the below script, and it worked as well.
DECLARE @ModDate datetime = '2019-12-04 00:00:00.000'

DECLARE @ModDateYear varchar(4) = CAST(DATEPART(YEAR,@ModDate) as varchar(4))

DECLARE @DynamicSQL varchar(max) =
'SELECT
       [AddressID]
    ,[AddressLine1]
    ,[AddressLine2]
    ,[City]
    ,[StateProvinceID]
    ,[PostalCode]
    ,[SpatialLocation]
    ,[rowguid]
    ,[ModifiedDate]
FROM [AdventureWorks' + @ModDateYear + '].[Person].[Address]
WHERE [ModifiedDate] <= ''' + CAST(@ModDate as varchar) + ''''

--select (@DynamicSQL)

EXEC (@DynamicSQL)
From the above examples, we could see that it’s somehow troublesome to build dynamic SQL which includes variables. Of course we could either include the variable definitions inside the dynamic SQL or invoke the values of the variables directly, but neither of them is neat enough, not to mention that in some extreme occasions neither could be used. That’s when sp_executesql stands up and takes the burden. With its help, we could easily re-write the above script as below
DECLARE @ModDate datetime = '2019-12-04 00:00:00.000'

DECLARE @ModDateYear varchar(4) = CAST(DATEPART(YEAR,@ModDate) as varchar(4))

DECLARE @DynamicSQL nvarchar(max) =
'SELECT
       [AddressID]
    ,[AddressLine1]
    ,[AddressLine2]
    ,[City]
    ,[StateProvinceID]
    ,[PostalCode]
    ,[SpatialLocation]
    ,[rowguid]
    ,[ModifiedDate]
FROM [AdventureWorks' + @ModDateYear + '].[Person].[Address]
WHERE [ModifiedDate] <= @ModDateVariable'

EXEC sp_executesql @DynamicSQL, N'@ModDateVariable datetime', @ModDateVariable = @ModDate
Please be noticed that I’ve changed the variable @DynamicSQL from type varchar(max) to nvarchar(max), as this is mandatory while using sp_executesql. Also, inside the @DynamicSQL definition I’ve changed the variable name to @ModDateVariable so that it’s not confused with the value variable @ModDate. Below are the explanations of how it works:
·         Firstly sp_executesql is a System stored procedure, so we need to use the EXEC statement to invoke it
·         @DynamicSQL – this is the dynamic SQL statement to be executed.
·         N'@ModDateVariable datetime' – this is the type definition for variables that’s inside the dynamic SQL statement
·         @ModDateVariable = @ModDate – this is to grant value to the variable @ModDateVariable
From the above simple example, we can see the beauty of sp_executesql – you only need to concentrate building your dynamic SQL, and then make declarations and grant values for all the variables later in a neat and convenient way.
Also, sp_executesql allows us to define output parameters. Let’s take a look at the below script
DECLARE @ModDate datetime = '2017-12-04 00:00:00.000'
DECLARE @PostCodeValue varchar(15)

DECLARE @DynamicSQL nvarchar(max) =
'SELECT
       @PostCode = [PostalCode]
FROM [AdventureWorks2017].[Person].[Address]
WHERE [ModifiedDate] <= @ModDateVariable AND AddressID = 1'

EXEC sp_executesql @DynamicSQL, N'@ModDateVariable datetime, @PostCode varchar(15) output', @ModDateVariable = @ModDate, @PostCode = @PostCodeValue output

SELECT @PostCodeValue
As we can see from the above script, inside the dynamic SQL we grant value to the variable @PostCode, and then we define that variable as an output parameter. We then grant its value to a pre-defined variable @PostCodeValue and so the SELECT statement displays the actual value of @PostCode.
Another feature about sp_executesql is that it caches the execution plan of the dynamic SQL we build. Next time when we execute the same dynamic SQL using sp_executesql, the cached plan will be fetched from memory so that we can save the recompile time. Remember that this feature can be a curse too. I encourage you to search online articles or books about parameter sniffing to dig deeper about this feature.


Comments

Post a Comment

Popular posts from this blog

SQL Server - Using SQL Agent JOBs to Schedule and Execute Multiple Stored Procedures in Parallel

This article introduces a way to schedule and kick off multiple stored procedures in parallel. Suppose we need to build an ETL system to migrate data from the source system to the target in T-SQL script; we've created multiple stored procedures (SP) each of which contains the payload of extracting data from one module of the source system and loading it to the target system; any of those stored procedures can be executed independently without relying on the execution statuses of any other stored procedures; and to speed up the whole process, we need to run multiple stored procedures simultaneously. Ideally, the above task can be handled easily in SSIS. Of course we could've created a Sequence Container and dropped a batch of SP execution tasks to it in SSIS, and then another Sequence Container containing the next batch of SP execution tasks, etc., but what if using SSIS has become a luxury for us (for example, your boss is not a fan of SSIS and prohibits you from using it as ...
Read more