none
Announcement: The Curse and Blessings of Dynamic SQL RRS feed

  • Discussão Geral

  • I have put up a new article on my web site entitled The Curse and Blessings of Dynamic SQL. Wait!, you say, haven't that article been up for a long time? Eh, no. Another article with the same name has been there. But rather than rewriting or reworking that article, I wrote a completely new one. I was not satisfied with the old one, and spoke about ancient things like SQL 2000 and old ADO.

    The new article includes a better chapter on SQL injection. There is a dynamic SQL style guide. There is a short introduction to dynamic pivot - where the PIVOT keyword is nowhere to be seen in the samples.

    I made this announcement here since some people like to link to the old article. You are more than welcome to link to the new article as well - the URL is the same - but obviously, you may want to read it, so that you know what you are linking to. I think the new article is a lot better, but I don't know what everyone else may think.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    sábado, 11 de janeiro de 2020 22:25

Todas as Respostas

  • Hi Erland,

    I've started to read your article and I'm curious if you address this immediate problem I had this Friday: how to output very long dynamic SQL? I use select @SQL, but the result I get in SSMS loses all carriage returns.

    Do you have any suggestions as how can we see generated SQL when it exceeds 8K characters?

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    domingo, 12 de janeiro de 2020 03:04
    Moderador
  • Hi Erland,

    I've started to read your article and I'm curious if you address this immediate problem I had this Friday: how to output very long dynamic SQL? I use select @SQL, but the result I get in SSMS loses all carriage returns.

    Do you have any suggestions as how can we see generated SQL when it exceeds 8K characters?

    That's indeed covered in section 5.2, The Necessity of Debug Prints.

    As it happens, I received another one in the mail this morning (that I have not played with myself yet):

    For my debug logic when using varchar(max) strings, I use this:

    IF @debug = 1 SELECT @sql AS [processing-instruction(x)] FOR XML PATH('');

    Then the Results window will create a URL that I click on to open a new XML window which I then copy all (Ctrl+A,Ctrl+C) and paste (Ctrl+V) into a new query window. I then remove the <?x and ?> wrappers. It retains the CR/LFs as well, of course.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se


    domingo, 12 de janeiro de 2020 10:52
  • Moderator, please make this announcement sticky for some period of time for visibility.

    Erland, thanks for taking the time to update your popular article.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    domingo, 12 de janeiro de 2020 11:51
  • I have SSMS 2014 and the result in grid came as one line without any CR/LF.

    Microsoft SQL Server Management Studio 14.0.17289.0
    Microsoft Analysis Services Client Tools 14.0.1016.283
    Microsoft Data Access Components (MDAC) 10.0.17134.1
    Microsoft MSXML 3.0 4.0 6.0 
    Microsoft Internet Explorer 9.11.17134.0
    Microsoft .NET Framework 4.0.30319.42000
    Operating System 6.3.17134

    I'm going to try to download version 18 later today.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    domingo, 12 de janeiro de 2020 13:34
    Moderador
  • I have SSMS 2014 and the result in grid came as one line without any CR/LF.


    Did you try Jonathan's suggestion? The one with FOR XML PATH above. I've added it to the article. It's quite slick. Also in SSMS 2014.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    domingo, 12 de janeiro de 2020 14:13
  • Nice tip! We could do with tips like these in TechNet Wiki.

      

    Best regards,
    Pete

      


    #PEJL
    Got any nice code? If you invest time in coding an elegant, novel or impressive answer on MSDN forums, why not copy it over to TechNet Wiki. Sharing is caring <3
    Have you got what it takes to become this month's TechNet Technical Guru? Join a long list of well known community big hitters, show your knowledge and prowess in your favoured technologies! Share the pain, teach others, save the day, from 101 to 1337, give a little back to those less awesome than you :D


    domingo, 12 de janeiro de 2020 14:19
    Moderador
  • Not yet, but I think our minimum version is SQL 2012 (I need to double check).

    UPDATE. Added this in two of my stored procedures and will use in a future too.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    domingo, 12 de janeiro de 2020 20:17
    Moderador
  • Not yet, but I think our minimum version is SQL 2012 (I need to double check).

    The solution with FOR XML PATH works on SQL 2005 and up.



    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    domingo, 12 de janeiro de 2020 20:55
  • It is a re-write of the article, not an update.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    domingo, 12 de janeiro de 2020 22:59
    Moderador