Dos and Don'ts of Dynamic SQL

  • Thom A

    SSC Guru

    Points: 98403

    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.

  • WolfgangE

    SSCertifiable

    Points: 6311

    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.

     

     

     

  • Thom A

    SSC Guru

    Points: 98403

    WolfgangE wrote:

    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.

  • alin.selicean

    SSC-Addicted

    Points: 495

    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.

    • This reply was modified 6 months, 2 weeks ago by  alin.selicean. Reason: Missed something :)
  • ahazelwood

    Grasshopper

    Points: 19

    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.

  • Aaron N. Cutshall

    SSCrazy Eights

    Points: 8725

    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:

    • \t = Tab
    • \r = Carriage Return
    • \n = Linefeed

    I simply perform a REPLACE function on them afterwards for their ASCII equivalents.

  • gary.rumble

    SSCommitted

    Points: 1716

    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
  • Thom A

    SSC Guru

    Points: 98403

    Aaron N. Cutshall wrote:

    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.

  • Thom A

    SSC Guru

    Points: 98403

    gary.rumble wrote:

    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;

    • This reply was modified 6 months, 2 weeks ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Scott Coleman

    One Orange Chip

    Points: 27400

    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.

  • TomD-982312

    SSC-Addicted

    Points: 434

    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 miss SQL Server so much. Now I'm working on Oracle and hate it

  • Jeff Moden

    SSC Guru

    Points: 994850

    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)".

    So what do you do when you introduce characters that will be entitized by XML?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Thom A

    SSC Guru

    Points: 98403

    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 Print (or a SELECT Statement 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?).

    • This reply was modified 6 months, 1 week ago by  Thom A. Reason: Underlining still broken
    • This reply was modified 6 months, 1 week ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jeff Moden

    SSC Guru

    Points: 994850

    Thom A wrote:

    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 Print (or a SELECT Statement 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?).

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994850

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. ๐Ÿ˜‰

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply