April 29, 2019 at 12:00 am
Comments posted to this topic are about the item Dos and Don'ts of Dynamic SQL
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 29, 2019 at 8:38 am
Hello there,
this is a nice article, and really sums up some important points.
I'd like to add 2 things 🙂
1)
As for formatting, I personally think putting the line break characters at the beginning, makes it even more readable. Additionally, I use variables for them. Your example in my way would be something like this:
declare @TAB nchar(1) = nchar(9);
declare @NEWLINE nchar(2) = nchar(13) + nchar(10);
DECLARE @TableName sysname = N'MyTable';
DECLARE @SQL nvarchar(MAX);
SET @SQL =
N'SELECT'
+ @NEWLINE + @TAB + N'@TableName AS TableName,'
+ @NEWLINE + @TAB + N'ID,'
+ @NEWLINE + @TAB + N'[name] AS CustomerName'
+ @NEWLINE + N'FROM'
+ @NEWLINE + @TAB + QUOTENAME(@TableName)
+ @NEWLINE + N'WHERE'
+ @NEWLINE + @TAB + 'ID = @ID'
+ @NEWLINE + @TAB + N'AND Status = ''Active'';';
PRINT @SQL;
--EXEC sp_executesql @SQL, N'@ID int', @ID = @ID;
2) Use comments to mark the origin of your code
When analysing the workload of your SQL instances, and watch at running queries, you don't know from which procedure the dynamic statement comes from. This sometimes makes is really difficult to find the procedure, which executed a problematic statement.
I like doing something like this, again using one of your examples as a basis:
CREATE PROC MyProc @ID int AS
BEGIN
DECLARE @SQL nvarchar(MAX);
declare @TAB nchar(1) = nchar(9);
declare @NEWLINE nchar(2) = nchar(13) + nchar(10);
SET @SQL =
N'-- dynamic query from ' + isnull( object_schema_name( @@procid ) + N'.' + object_name( @@procid ), '' )
+ @NEWLINE + N'SELECT *'
+ @NEWLINE + 'FROM MyTable'
+ @NEWLINE + 'WHERE ID = ' + CONVERT(nvarchar(MAX),@ID);
EXEC(@SQL);
END;
go
I use the variable @@PROCID, so even when the procedure is renamed one day, the dynamic string contains the correct name of the procedure.
The isnull() I use because: if you one day copy out the procedure code and try to execute it, @@PROCID is null in an ad hoc query, thus leading the whole query string to NULL. Then you wonder why nothing happens, and this is annoying to find 😀
Thank you for reading.
April 29, 2019 at 9:00 am
Additionally, I use variables for them.
I really like the idea of putting the line break and carriage return in a variable, that would certainly make the code a little more succinct and may well make it more readable for users less familiar with the ASCII/Unicode numbers for Carriage Return and Line Break. Something I'll keep in mind of in the future.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 29, 2019 at 11:43 am
Nice article. What I usually do, is use what I call SQL snippets and placeholders which I later use to replace / inject what I need. For example:
SQL snippet:
DECLARE @sql VARCHAR(MAX) = ''
DECLARE @join_needed BIT = 0
SET @sql = '
USE [##DB##]
DECLARE @orderstate INT = ##STATE##
SELECT
t1.OrderID
,t1.OrderDate
##CUSTOMERCOLUMNS##
FROM dbo.Orders t1
##JOIN##
WHERE 1=1
t1.OrderStatus = @orderstate
##CUSTOMERSTATE##
'
SET @SQL = REPLACE(@sql, '##STATE##', 1)
IF @join_needed = 0
BEGIN
SET @sql = REPLACE(@sql, '##JOIN##', '')
SET @sql = REPLACE(@sql, '##CUSTOMERCOLUMNS##', '')
SET @sql = REPLACE(@sql, '##CUSTOMERSTATE##', '')
END
ELSE
BEGIN
SET @sql = REPLACE(@sql, '##JOIN##', 'INNER JOIN dbo.Customers t2 ON t1.[CustomerID] = t2.[Id]')
SET @sql = REPLACE(@sql, '##CUSTOMERCOLUMNS##', ',t2.[Name] AS [CustomerName], t2.[Address] AS [CustomerAddress]')
SET @sql = REPLACE(@sql, '##CUSTOMERSTATE##', 'AND t2.[State] = 1 /* Only active customers */')
END
EXEC(@sql)
By replacing the ## placeholders with proper values, based on the required logic, I end up with the final script needed for the specified context. This is a simplified example, I am using this approach in a more complex scenarios, where I might have multiple IFs and so on. By using local variables inside the dynamic SQL, I can (up to a point) mitigate any malicious values and also, one can extend the functionality by adding logic to check the user input.
Just my 2c on this... 🙂 Hope this helps somebody.
April 29, 2019 at 1:00 pm
For cases where we have dynamic sql, there is an additional parameter labelled @debug bit = 0 in the arguments.
At the end of the script, there is a check for this flag being set:
if @debug = 1
print @sql;
else -- Execute the script.
Extremely helpful in debugging the stored procedure to see what is actually being generated. We will also usually print out the actual parameters as part of the dynamic sql in this case to be able to execute it manually if necessary.
April 29, 2019 at 2:51 pm
Great article Thom! I've been working on a SQL Saturday presentation right along these lines. There were a couple of points that I'd like to include in my presentation with your permission. I really like the object validation approach!
As for the CR/LF characters, one thing that I've done when generating lots of dynamic SQL (like when I'm building a dynamic stored procedure) is to embed characters in my string that are later replaced en masse after it's all built. For example:
I simply perform a REPLACE function on them afterwards for their ASCII equivalents.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
April 29, 2019 at 3:16 pm
When I do it in SSMS I use the fact that it allows multiple lines in a single quoted string, e.g.
declare @sql nvarchar(2000)
set @sql =
'select
*
from SomeTable st
join AnotherTable at on at.foreignKey = st.id
where st.someColumn = 3'
print @sql
select
*
from SomeTable st
join AnotherTable at on at.foreignKey = st.id
where st.someColumn = 3
14090 SW TENNESSEE LN
April 29, 2019 at 3:25 pm
Great article Thom! I've been working on a SQL Saturday presentation right along these lines. There were a couple of points that I'd like to include in my presentation with your permission. I really like the object validation approach!
I have no problems with you quoting and citing some of the article Aaron, however, although I retrain the owner ship it is hosted by SSC and therefore falls under their Terms of Use too. Not sure what their stance is on articles written by a contributor, but section 4.3 seems to suggest no. Might be a question for Steve.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 29, 2019 at 3:29 pm
When I do it in SSMS I use the fact that it allows multiple lines in a single quoted string
I'm not actually a fan of that method. It's fine when your writing something like that, but the problem there is that that query isn't dynamic. If you start writing statements like the one below, the method falls over, as you can't nicely format both the inner and outer queries:
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'INSERT INTO dbo.Emails(Email)' + NCHAR(13) + NCHAR(10) +
STUFF((SELECT NCHAR(13) + NCHAR(10) +
N'UNION ALL' + NCHAR(13) + NCHAR(10) +
N'SELECT Email' + NCHAR(13) + NCHAR(10) +
N'FROM ' + QUOTENAME(t.[name])
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.[name] = N'Email'
FOR XML PATH(N''),TYPE).value(N'.','nvarchar(MAX)'),1,13,'') + N';';
PRINT @SQL;
You'd end up with something like the below, which I find a little messy:
DECLARE @SQL nvarchar(MAX);
SET @SQL = N'INSERT INTO dbo.Emails(Email)
' +
STUFF((SELECT N'UNION ALL
SELECT Email
FROM ' + QUOTENAME(t.[name])
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.[name] = N'Email'
FOR XML PATH(N'')),1,9,'') + N';';
EXEC sp_executesql @SQL;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 29, 2019 at 6:18 pm
I prefer the multi-line literal template with replaceable tokens style myself, like that shown in Alin Selicean's comment above.
But if you must use explicit line breaks, a single line feed (NCHAR(10)) works fine. The carriage return is completely unnecessary.
May 6, 2019 at 12:17 pm
May 6, 2019 at 7:41 pm
Like the article. I'd like to point to the fact that "PRINT @sql;" is limited to 4000 characters, therefore I use "SELECT CAST('<root><![CDATA[' + @sql + ']]></root>' AS XML)".
So what do you do when you introduce characters that will be entitized by XML?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2019 at 7:55 am
Like the article. I'd like to point to the fact that "PRINT @sql;" is limited to 4000 characters, therefore I use "SELECT CAST('<root><![CDATA[' + @sql + ']]></root>' AS XML)".
I cover that in the article, but I'll underline for emphasis. 🙂
Using
SELECTStatement if your dynamic SQL is over 4,000 characters) you can inspect the SQL that is about to be run.
I don't see the reason to use XML, when a SELECT and a copy and paste will do the trick; that's adding a unneeded layer (and, like Jeff said, what do you do if you have characters that can't be displayed in XML?).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 7, 2019 at 2:14 pm
TomD-982312 wrote:Like the article. I'd like to point to the fact that "PRINT @sql;" is limited to 4000 characters, therefore I use "SELECT CAST('<root><![CDATA[' + @sql + ']]></root>' AS XML)".
I cover that in the article, but I'll underline for emphasis. 🙂
Using
SELECTStatement if your dynamic SQL is over 4,000 characters) you can inspect the SQL that is about to be run.I don't see the reason to use XML, when a
SELECTand a copy and paste will do the trick; that's adding a unneeded layer (and, like Jeff said, what do you do if you have characters that can't be displayed in XML?).
Just to answer that question, here's what I use to display "long strings". I learned the base of the XML code (I suck at XML) from Orlando Colamatteo and got tired of typing the code over and over.
CREATE FUNCTION [dbo].[ShowLongString]
/**********************************************************************************************************************
Purpose:
Display a string of more than 8000 characters. The string can be Dynamic SQL, XML, or just about anything else.
Note that this function does use XML and, while normally quite faithful, there are characters (I've not taken the time
to identify them because they're edge cases that I don't deal with) that it just won't handle. It will, however,
handle most control-characters below ASCII 32.
-----------------------------------------------------------------------------------------------------------------------
Usage:
--===== Example with Dynamic SQL
DECLARE @SQL VARCHAR(MAX);
SELECT @SQL = '
SELECT somecolumnlist
FROM some table with joins
;'
;
SELECT LongString
FROM dbo.ShowLongString(@SQL)
;
--===== Example with a call to a table or view
SELECT sm.Object_ID, Definition = ls.LongString
FROM sys.SQL_Modules sm
CROSS APPLY dbo.ShowLongString(sm.Definition) ls
;
-----------------------------------------------------------------------------------------------------------------------
Credits:
1. I learned this trick from a post by Orlando Colamatteo at the following link. It has served me very well since
then. Thanks, Orlando.
https://www.sqlservercentral.com/Forums/FindPost1468782.aspx
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
**********************************************************************************************************************/
--===== Declare the I/O for this function
(@pLongString VARCHAR(MAX))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT LongString =
(
SELECT REPLACE(
CAST(
'--' + CHAR(10) + @pLongString + CHAR(10)
AS VARCHAR(MAX))
,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
AS [processing-instruction(LongString)]
FOR XML PATH(''), TYPE
)
;
GO
GRANT SELECT ON [dbo].[ShowLongString] TO [public] AS [dbo]
--Jeff Moden
Change is inevitable... Change for the better is not.
May 7, 2019 at 2:20 pm
On a different note, I'm really happy that they fixed the "code wrapping" and indentation issues on this forum but, unless they limit the vertical height of the code window, that horizontal scroll bar is pretty useless and annoying at best.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply