Written by 12:37 SQL Server • 1 Comment

10 SP_EXECUTESQL Gotchas to Avoid for Better Dynamic SQL

CodingSight - 10 SP_EXECUTESQL Gotchas to Avoid for Better Dynamic SQL

Do you know how powerful a tool like dynamic SQL can be? Use it the wrong way, and you can allow someone to take over your database. Plus, there might be too much complexity. This article aims to introduce the pitfalls when using SP_EXECUTESQL and offers 10 most common gotchas to avoid.

SP_EXECUTESQL is one of the ways you can run SQL commands embedded in a string. You build this string dynamically through the code. That’s why we call this dynamic SQL. Aside from a series of statements, you can also pass into it a list of parameters and values. In fact, these parameters and values differ from the EXEC command. EXEC does not accept parameters for dynamic SQL. Yet, you execute SP_EXECUTESQL using EXEC!

For a newbie to dynamic SQL, here’s how you invoke this.

EXEC sp_executesql <command string>[, <input or output parameters list>, <parameter value1>, <parameter value n>]

You form the string of commands that include valid SQL statements. Optionally, you can pass a list of input or output parameters and their data types. And finally, you pass a comma-separated list of values. If you pass parameters, you need to pass values. Later, you will see both right and wrong examples of this as you read on.

Using SP_EXECUTESQL When You Don’t Need It

That’s right. If you don’t need it, don’t use it. If this becomes the 10 commandments of SP_EXECUTESQL, this is the first one. It is because this system procedure can be easily abused. But how do you know?

Answer this: Is there a problem if the command in your dynamic SQL becomes static? If you have nothing to say on this point, then you don’t need it. See the example.

DECLARE @sql NVARCHAR(100) = N'SELECT ProductID, Name FROM Production.Product ' +
			      'WHERE ProductID = @ProductID';
DECLARE @paramsList NVARCHAR(100) = N'@ProductID INT';
DECLARE @param1Value INT = 1;

EXEC sp_executesql @sql, @paramsList, @param1Value
GO

As you can see, the usage of SP_EXECUTESQL is complete with a command string, parameter, and value. But does it need to be this way? Surely not. It’s perfectly fine having this:

DECLARE @productID INT = 1;

SELECT ProductID, Name
FROM Production.Product
WHERE ProductID = @productID;

But I might be embarrassed when you see the examples further in the article. Because I will contradict what I am claiming in this very first point. You will see short dynamic SQL statements that are better than static. So, bear with me because the examples will just prove the points outlined here. For the rest of examples, pretend for a while that you’re looking at the code meant for dynamic SQL.

Out-of-Scope Objects and Variables

Running a series of SQL commands in a string using SP_EXECUTESQL is like creating a nameless stored procedure and running it. Knowing this, objects like temporary tables and variables outside the command string will be out of scope. Because of this, runtime errors will occur.

When Using SQL Variables

Check this out.

DECLARE @extraText VARCHAR(10) = 'Name is '; -- note this variable
DECLARE @sql NVARCHAR(100) = N'SELECT @extraText + FirstName + SPACE(1) + LastName
                               FROM Person.Person WHERE BusinessEntityID = @BusinessEntityID';
DECLARE @paramList NVARCHAR(100) = N'@BusinessEntityID INT';
DECLARE @param1Value INT = 1;

EXEC sp_executesql @sql, @paramList, @BusinessEntityId = @param1Value;
GO

The variable @extraText is invisible to commands executed. Instead of this, a literal string or the variable declared inside the dynamic SQL string is much better. Anyway, the result is:

Variable out of scope outside the dynamic SQL string
Figure 1. Variable out of scope outside the dynamic SQL string.

Did you see that error in Figure 1? If you need to pass a value inside the dynamic SQL string, add another parameter.

When Using Temporary Tables

Temporary tables in SQL Server also exist within the scope of a module. So, what do you think about this code?

DECLARE @sql NVARCHAR(200) = N'SELECT BusinessEntityID, LastName, FirstName, MiddleName
                               INTO #TempNames
                               FROM Person.Person
                               WHERE BusinessEntityID BETWEEN 1 and 100';

EXEC sp_executesql @sql;
EXEC sp_executesql N'SELECT * FROM #TempNames'
GO

The code above is executing 2 stored procedures in succession. The temporary table created from the first dynamic SQL won’t be accessible to the second. As a result, you will get an Invalid Object Name #TempNames error.

SQL Server QUOTENAME Mistake

Here’s another example that will look fine at first glance but will cause an Invalid Object Name error. See the code below.

DECLARE @sql NVARCHAR(100) = N'SELECT * FROM @Table';
DECLARE @tableName NVARCHAR(20) = 'Person.Person';

SET @sql = REPLACE(@sql,'@Table',QUOTENAME(@tableName));

PRINT @sql;
EXEC sp_executesql @sql;
GO

To be valid for SELECT, enclose the schema and the table with square brackets like this: [Schema].[Table]. Or don’t enclose them at all (Unless the table name includes one or more spaces). In the example above, no square brackets were used for both the table and schema. Instead of using @Table as a parameter, it became a placeholder for REPLACE. The QUOTENAME was used.

QUOTENAME encloses a string with a delimiter. This is also good for dealing with single quotes in the dynamic SQL string. The square bracket is the default delimiter. So, in the example above, what do you think QUOTENAME did? Check Figure 2 below.

Square brackets enclosed both schema and table as one with QUOTENAME
Figure 2. Square brackets enclosed both schema and table as one with QUOTENAME.

The SQL PRINT statement helped us debug the problem by printing the dynamic SQL string. Now we know the problem. What’s the best way to fix this? One solution is the code below:

DECLARE @sql NVARCHAR(100) = N'SELECT COUNT(*) FROM @Table';
DECLARE @tableName NVARCHAR(20) = 'Person.Person';

SET @sql = REPLACE(@sql,'@Table',QUOTENAME(PARSENAME(@tableName,2)) + '.'
                               + QUOTENAME(PARSENAME(@tableName,1)));
PRINT @sql;
EXEC sp_executesql @sql;
GO

Let’s explain this.

First, @Table is used as a placeholder for REPLACE. It will search for the occurrence of @Table and replace it with the correct values. Why? If this is used as a parameter, an error will occur. That is also the reason for using REPLACE.

Then, we used PARSENAME. The string we passed to this function will separate it to schema and table. PARSENAME(@tableName,2) will get the schema. While PARSENAME(@tableName,1) will get the table.

Finally, QUOTENAME will enclose the schema and table names separately after PARSENAME is done. The result is [Person].[Person]. Now, it is valid.

However, a better way to sanitize the dynamic SQL string with object names will be shown later.

Executing SP_EXECUTESQL with a NULL Statement

There are days when you’re upset or downhearted. Mistakes can be made along the way. Then add a long dynamic SQL string to the mix and NULLs. And the result?

Nothing.

SP_EXECUTESQL will give you a blank result. How? By concatenating a NULL by mistake. Consider the example below:

DECLARE @crlf NCHAR(2);
DECLARE @sql NVARCHAR(200) = N'SELECT' + @crlf +
	' p.Name AS Product' + @crlf +
	',v.Name AS Vendor' + @crlf +
	',v.AccountNumber' + @crlf +
	',p.ListPrice' + @crlf +
	'FROM Purchasing.ProductVendor pv' + @crlf +
	'INNER JOIN Production.Product p ON pv.ProductID = p.ProductID' + @crlf +
	'INNER JOIN Purchasing.Vendor v ON pv.BusinessEntityID = v.BusinessEntityID' + @crlf +
	'WHERE pv.BusinessEntityID = @BusinessEntityID';
DECLARE @paramsList NVARCHAR(100) = N'@BusinessEntityID INT';
DECLARE @BusinessEntityID INT = 1500;

PRINT @sql;
EXEC sp_executesql @sql, @paramsList, @BusinessEntityID
GO

At first, the code looks good. Yet the eagle eyes among us will notice the @crlf variable. Its value? The variable was not initialized. So, it’s NULL.

But what’s the point of that variable? In a later section, you will know how important it is on formatting and debugging. For now, let’s focus on the point at hand.

First, concatenating a NULL variable to the dynamic SQL string will result to NULL. Then, PRINT will print blank. Finally, SP_EXECUTESQL will run fine with the NULL dynamic SQL string. But it returns nothing.

NULLs can mesmerize us on an already bad day. Take a short break. Relax. Then come back with a clearer mind.

Inlining Parameter Values

Messy.

That’s how inlining values to dynamic SQL string will look like. There will be lots of single quotes for strings and dates. If you’re not careful, the O’Briens and O’Neils will cause errors too. And since dynamic SQL is a string, you must CONVERT or CAST the values to string. Here’s an example.

DECLARE @shipDate DATETIME = '06/11/2011';
 DECLARE @productID INT = 750;
 DECLARE @sql NVARCHAR(1000);
 SET @sql = N'SELECT
  soh.ShipDate
 ,sod.ProductID
 ,SUM(sod.OrderQty) AS TotalQty
 ,SUM(sod.LineTotal) AS LineTotal
 FROM Sales.SalesOrderHeader soh
 INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
 WHERE soh.ShipDate BETWEEN ' + '''' + CONVERT(VARCHAR(10), @shipDate, 101) + '''' + ' AND DATEADD(MONTH,1,' + '''' + CONVERT(VARCHAR(10), @shipDate, 101) + ''') ' +
 'AND sod.ProductID = ' + CAST(@productID AS VARCHAR(8)) +
 ' GROUP BY soh.ShipDate, sod.ProductID' +
 ' ORDER BY sod.ProductID';
 
 PRINT @sql;
 EXEC sp_executesql @sql;

I saw messier dynamic strings than this. Notice the single quotes, CONVERT, and CAST. If parameters were used, this could look better. You can see it below

DECLARE @shipDate DATETIME = '06/11/2011';
 DECLARE @productID INT = 750;
 DECLARE @sql NVARCHAR(1000);
 DECLARE @paramList NVARCHAR(500) = N'@shipDate DATETIME, @productID INT';
 SET @sql = N'SELECT
  soh.ShipDate
 ,sod.ProductID
 ,SUM(sod.OrderQty) AS TotalQty
 ,SUM(sod.LineTotal) AS LineTotal
 FROM Sales.SalesOrderHeader soh
 INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
 WHERE soh.ShipDate BETWEEN @shipDate AND DATEADD(MONTH,1,@shipDate)
 AND sod.ProductID = @productID
  GROUP BY soh.ShipDate, sod.ProductID
  ORDER BY sod.ProductID';

PRINT @sql;
EXEC sp_executesql @sql, @paramList, @shipDate, @productID
GO

See? Less single quotes, no CONVERT and CAST, and cleaner too.

But there’s an even more dangerous side-effect of inline values.

SQL Injection

If we lived in a world where all people were good, SQL injection would never be thought of. But that is not the case. Someone might inject malicious SQL code into yours. How can this happen?

Here’s the scenario we are going to use in our example:

  • Values are fused to the dynamic SQL string like our example earlier. No parameters.
  • The dynamic SQL string is up to 2GB using NVARCHAR(MAX). A lot of space to inject malicious code.
  • Worst, the dynamic SQL string is executed with elevated permissions.
  • The SQL Server instance accepts SQL authentication.

Is this too much? For systems managed by one man, this can happen. Nobody checks on him anyway. For larger companies, an IT department lax with security sometimes exists.

Is this still a threat today? It is, according to cloud service provider Akamai in their State of the Internet/Security report. Between November 2017 to March 2019, SQL injection represents nearly two-thirds of all web application attacks. That’s the highest of all the threats examined. Very bad.

Do you want to see it for yourself?

SQL Injection Practice: Bad Example

Let’s do some SQL injection in this example. You can try this in your own AdventureWorks database. But make sure that SQL authentication is allowed, and you run it with elevated permissions.

DECLARE @lastName NVARCHAR(MAX) = 'Mu';
DECLARE @firstName NVARCHAR(MAX) = 'Zheng''; CREATE LOGIN sà WITH PASSWORD=''12345''; ALTER SERVER ROLE sysadmin ADD MEMBER sà; --';
DECLARE @crlf NCHAR(2) = nchar(13) + nchar(10);

DECLARE @sql NVARCHAR(MAX) = N'SELECT ' + @crlf +
' p.LastName ' + @crlf +
',p.FirstName ' + @crlf +
',a.AddressLine1 ' + @crlf +
',a.AddressLine2 ' + @crlf +
',a.City ' + @crlf +
'FROM Person.Person p ' + @crlf +
'INNER JOIN Person.BusinessEntityAddress bea ON p.BusinessEntityID = bea.BusinessEntityID ' + @crlf +
'INNER JOIN Person.Address a ON bea.AddressID = a.AddressID ' + @crlf +
'WHERE p.LastName = ' + NCHAR(39) + @lastName + NCHAR(39) + ' ' + @crlf +
'AND p.FirstName = '  + NCHAR(39) + @firstName + NCHAR(39);

-- SELECT @sql;	-- uncomment if you want to see what's in @sql					
EXEC sp_executesql @sql;
GO

The code above doesn’t represent actual code from an existing company. It’s not even callable by an app. But this illustrates the evil deed. So, what do we have here?

First, the injected code will create a SQL account that looks like sa, but it’s not. And like sa, this has sysadmin permissions. Eventually, this will be used to access the database anytime with full privileges. Anything is possible once this is done: stealing, deleting, tampering of corporate data, you name it.

Will this code run? Definitely! And once it is, the super account will be created silently. And, of course, the address of Zheng Mu will appear in the result set. Everything else is normal. Shady, don’t you think?

After you run the above code, try running this, too:

SELECT IS_SRVROLEMEMBER('sysadmin','sà')

If it returns 1, he’s in, whoever this  is. Alternatively, you can check it in your SQL Server’s Security Logins in SQL Server Management Studio.

So, what did you get?

Scary, isn’t it? (If this is real, it is.)

SQL Injection Practice: Good Example

Now, let’s change the code a bit by using parameters. The other conditions are still the same.

DECLARE @lastName NVARCHAR(MAX) = 'Mu';
DECLARE @firstName NVARCHAR(MAX) = 'Zheng''; CREATE LOGIN sà WITH PASSWORD=''12345''; ALTER SERVER ROLE sysadmin ADD MEMBER sà; --';
DECLARE @crlf NCHAR(2) = nchar(13) + nchar(10);

DECLARE @sql NVARCHAR(MAX) = N'SELECT ' + @crlf +
' p.LastName ' + @crlf +
',p.FirstName ' + @crlf +
',a.AddressLine1 ' + @crlf +
',a.AddressLine2 ' + @crlf +
',a.City ' + @crlf +
'FROM Person.Person p ' + @crlf +
'INNER JOIN Person.BusinessEntityAddress bea ON p.BusinessEntityID = bea.BusinessEntityID ' + @crlf +
'INNER JOIN Person.Address a ON bea.AddressID = a.AddressID ' + @crlf +
'WHERE p.LastName = @lastName' + @crlf +
'AND p.FirstName = @firstName';

DECLARE @paramList NVARCHAR(300) = N'@lastName NVARCHAR(50), @firstName NVARCHAR(50)';

-- SELECT @sql;	-- uncomment if you want to see what's in @sql
EXEC sp_executesql @sql, @paramList, @lastName, @firstName;
GO

There are 2 differences in the result compared to the first example.

  • First, Zheng Mu’s address won’t appear. The result set is blank.
  • Then, the renegade account is not created. Using IS_SRVROLEMEMBER will return NULL.

What happened?

Since parameters are used, the value of @firstName is ‘Zheng”; CREATE LOGIN sà WITH PASSWORD=”12345”; ALT’. This is taken as a literal value and truncated to 50 characters only. Check the first name parameter in the code above. It’s NVARCHAR(50). That’s why the result set is blank. No person with a first name like that is in the database.

This is just one example of SQL injection and one way to avoid it. There’s more involved in doing actual thing. But I hope I made the point clear why inline values in dynamic SQL are bad.

Parameter Sniffing

Have you experienced a slow-running stored procedure from an app, but when you tried running it in SSMS it became fast? It’s baffling because you used the exact parameter values used in the app.

That’s parameter sniffing in action. SQL Server creates an execution plan the first time the stored procedure is run or recompiled. Then, reuse the plan for the next run. That sounds great because SQL Server doesn’t need to recreate the plan every time. But there are times that a different parameter value needs a different plan to run fast.

Here’s a demonstration using SP_EXECUTESQL and a plain static SQL.

DECLARE @sql NVARCHAR(150) = N'SELECT Name FROM Production.Product WHERE ProductSubcategoryID = @ProductSubcategoryID';
DECLARE @paramList NVARCHAR(100) = N'@ProductSubcategoryID INT';
DECLARE @ProductSubcategoryID INT = 23;

EXEC sp_executesql @sql, @paramList, @ProductSubcategoryID

This one is very simple. Check the execution plan in Figure 3.

Execution plan of a query executed using SP_EXECUTESQL
Figure 3. Execution plan of a query executed using SP_EXECUTESQL.

Now let’s try the same query using static SQL.

DECLARE @ProductSubcategoryID INT = 23;
SELECT Name FROM Production.Product WHERE ProductSubcategoryID = @ProductSubcategoryID

Check out Figure 4, then compare it to Figure 3.

Execution plan of the same query using static SQL
Figure 4. Execution plan of the same query using static SQL.

In Figure 3, Index Seek and Nested Loop are used. But in Figure 4, it’s a Clustered Index Scan. While there’s no discernible performance penalty at this point, this shows parameter sniffing is not just an imagination.

This can be very frustrating once the query becomes slow. You might end up using techniques like recompiling or using query hints to avoid it. Any of these has drawbacks.

Unformatted Dynamic SQL String in SP_EXECUTESQL

What can go wrong with this code?

DECLARE @sql NVARCHAR(100) = N'SELECT COUNT(*) AS ProductCount' +
                              'FROM Production.Product';
PRINT @sql;
EXEC sp_executesql @sql;

It’s short and simple. But check Figure 5 below.

Missing space between column alias and FROM keyword in dynamic SQL string caused an error
Figure 5. Missing space between column alias and FROM keyword in dynamic SQL string caused an error.

Errors occur if you don’t mind a single space between keywords and objects when forming the dynamic SQL string. Like in Figure 5, where the ProductCount column alias and the keyword FROM have no space in between. It becomes confusing once a part of a string flows down to the next line of code. It makes you think that the syntax is correct.

Notice also that the string used 2 lines in the code window, but the output of PRINT shows 1 line. Imagine if this is a very, very long command string. It’s hard to find the problem until you properly format the string from the Messages tab.

To solve this problem, add a carriage return and line feed. You probably notice a variable @crlf from the previous examples. Formatting your dynamic SQL string with space and a new line will make the dynamic SQL string more readable. This is great for debugging too.

Consider a SELECT statement with JOIN. It needs several lines of code like the example below.

DECLARE @sql NVARCHAR(400)
DECLARE @shipDate DATETIME = '06/11/2011';
DECLARE @paramList NVARCHAR(100) = N'@shipDate DATETIME';
DECLARE @crlf NCHAR(2) = NCHAR(13) + NCHAR(10);

set @sql = N'SELECT ' + @crlf +
 'soh.ShipDate ' + @crlf +
 ',sod.ProductID ' + @crlf +
 ',SUM(sod.OrderQty) AS TotalQty ' + @crlf +
 ',SUM(sod.LineTotal) AS LineTotal ' + @crlf +
 'FROM Sales.SalesOrderHeader soh ' + @crlf +
 'INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID ' + @crlf +
 'WHERE soh.ShipDate = @shipDate' + @crlf +
 'GROUP BY soh.ShipDate, sod.ProductID ' + @crlf +
 'ORDER BY sod.ProductID';

 PRINT @sql;
 EXEC sp_executesql @sql,@paramList,@shipDate
 GO

To format the string, the @crlf variable is set to NCHAR(13), a carriage return, and NCHAR(10), a line feed. It is concatenated to each line to break a long string of SELECT statement. To see the result in the Messages tab, we use PRINT. Check the output in Figure 6 below.

Figure 6. A formatted dynamic SQL string flowing down to several lines of code.

How you form the dynamic SQL string is up to you. Whatever suits you to make it clear, readable, and easy to debug when the time comes.

Unsanitized Object Names

Do you need to dynamically set table, view, or database names for any reason? Then you need to “sanitize” or validate these object names to avoid errors.

In our example, we will use a table name, though the validation principle may apply to views as well. How you deal with it next will be different.

Earlier, we use PARSENAME to separate schema name from the table name. It can also be used if the string has a server and database name. But in this example, we will use schema and table names only. I leave the rest to your brilliant minds. This will work regardless if you name your tables with or without spaces. Spaces on table or view names are valid. So, it works for dbo.MyFoodCravings or [dbo].[My Food Cravings].

EXAMPLE

Let’s create a table.

CREATE TABLE [dbo].[My Favorite Bikes]
(
	id INT NOT NULL,
	BikeName VARCHAR(50)
)
GO

Then we create a script that will use SP_EXECUTESQL. This will run a generic DELETE statement for any table given a 1-column key. The first thing to do is to parse the full object name.

DECLARE @object NVARCHAR(128) = '[dbo].[My Favorite Bikes]';
DECLARE @schemaName NVARCHAR(128) = PARSENAME(@object,2);
DECLARE @tableName NVARCHAR(128) = PARSENAME(@object,1);

This way we separate the schema from the table. To validate further, we use OBJECT_ID. If it’s not NULL, then it’s valid.

IF NOT OBJECT_ID(QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)) IS NULL
BEGIN
	PRINT @object + ' is valid!'
	-- do the rest of your stuff here
END
ELSE
BEGIN
        PRINT 'Invalid object name ' + @object
	-- if you need to do anything else, insert it here
END

Notice also that we used QUOTENAME. This will ensure that table names with space won’t trigger an error by enclosing them with square brackets.

But how about validating the key column? You can check the existence of the column of the target table in sys.columns.

IF (SELECT COUNT(*) FROM sys.columns
	    WHERE [object_id] = OBJECT_ID(QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName))
		  AND [name] = @idKey) > 0
BEGIN
     -- add miscellaneous code here, if needed
     EXEC sp_executesql @sql, @paramsList, @id
END
ELSE
BEGIN
     PRINT 'Invalid column name ' + @idKey + ' for object ' + @object
     -- if you need to do anything else, insert it here
END

Now, here’s the full script to what we want to accomplish.

DECLARE @object NVARCHAR(128) = '[dbo].[My Favorite Bikes]';
DECLARE @schemaName NVARCHAR(128) = PARSENAME(@object,2);
DECLARE @tableName NVARCHAR(128) = PARSENAME(@object,1);
DECLARE @isDebug BIT = 1;
DECLARE @idKey NVARCHAR(128) = N'id';

DECLARE @sql NVARCHAR(200) = N'DELETE FROM @object WHERE @idKey = @id';
DECLARE @id INT = 0;
DECLARE @paramList NVARCHAR(100) = N'@id INT';

IF NOT OBJECT_ID(QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName)) IS NULL
BEGIN
   PRINT @object + ' is valid!'
   
   IF (SELECT COUNT(*) FROM sys.columns
       WHERE [object_id] = OBJECT_ID(QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName))
         AND [name] = @idKey) > 0
   BEGIN
       SET @sql = REPLACE(@sql, '@object', QUOTENAME(@schemaName) + '.' +          
                  QUOTENAME(@tableName));
       SET @sql = REPLACE(@sql, '@idkey', QUOTENAME(@idKey));
       IF @isDebug = 1
	   PRINT @sql;
       EXEC sp_executesql @sql, @paramList, @id
   END
   ELSE
       PRINT 'Invalid column name ' + @idKey + ' for object ' + @object
END
ELSE
BEGIN
   PRINT 'Invalid object name ' + @object
   -- if you need to do anything else, insert it here
END
GO

The result of this script is in Figure 7 below.

Figure 7. Result of the generic script to delete a record from any table given a key and value using dynamic SQL and SP_EXECUTESQL.

You can try this with other tables. Simply change the @object@idkey, and @id variable values.

No Provision for Debugging

Errors can happen. So, you need to know the generated dynamic SQL string to find the root cause. We are not fortune tellers or magicians to guess the form of the dynamic SQL string. So, you need a debug flag.

Notice in Figure 7 earlier that the dynamic SQL string is printed in the Messages tab of SSMS. We added an @isDebug BIT variable and set it to 1 in code. When the value is 1, the dynamic SQL string will print. This is good if you need to debug a script or stored procedure like this. Just set it back to zero when you’re done debugging. If this is a stored procedure, make this flag an optional parameter with a default value of zero.

To see the dynamic SQL string, you can use 2 possible methods.

  • Use PRINT if the string is less than or equal to 8000 characters.
  • Or use SELECT if the string is more than 8000 characters.

Poorly Performing Dynamic SQL Used in SP_EXECUTESQL

SP_EXECUTESQL can be slow if you assign a slow-running query to it. Period. This does not involve problems with parameter sniffing yet.

So, start static with the code you want to run dynamically. Then, check the Execution Plan and STATISTICS IO. See if there are missing indexes that you need to create. Tune it early.

The Bottomline in SP_EXECUTESQL

Using SP_EXECUTESQL is like wielding a powerful weapon. But the one who wields it needs to be skilled at it. Though this is also not rocket science. If you’re a newbie today, it can become common sense in time with practice.

This list of gotchas is not complete. But it covers the common ones. If you need more information, check out these links:

Like this? Then please share it on your favorite social media platforms. You can also share with us your time-tested tips in the Comments section.

(Visited 417 times, 4 visits today)

Subscribe to our digest
to get SQL Server industry insides!

Close