Dynamic SQL for beginners in 33 minutes

  • arthur

    SSC Enthusiast

    Points: 140

    Comments posted to this topic are about the item Dynamic SQL for beginners in 33 minutes

  • Jeff Moden

    SSC Guru

    Points: 994667

    I hate these types of things even on YouTube even if it's a worthwhile lesson.  The computer generated voice is rather monotonous and makes a lot of mistakes in pronunciation, etc.  Then you have to sit there and watch the computer generated presentation painfully type line by agonizing line and it takes twice as long to even do that because someone is writing 1 insert per row instead of using the power of the VALUES clause.  Instead of watching the machine type data inputs, just show the data inputs and tell people that the code is available in an attachment to the submittal.  Doing so will also allow you to lose the awful background music that you're using to fill in otherwise dead time.

    To make matters worse, if you actually want to try out the code, you have to type it all in from the screen because you can't do a copy and paste of the code from a video.

    It's a real shame for this particular submittal because, especially for the first video, it's a really important subject and a whole lot of explanation and emphasis was left out.

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

  • petehughes247

    SSC Enthusiast

    Points: 100

    Such a shame - that computer-generated voice ... "ess-skew-ell" is soooo annoying I stopped the video after less than a minute 🙁 Take the time to write a script and read it to your viewers, please 🙂

  • Thom A

    SSC Guru

    Points: 98326

    This is quite a damning reply I'm afraid, but dynamic SQL is something that I feel needs to be done right, and taught right, and this simply doesn't; far from it.

    I only skimmed through the first video, but why is it using "dynamic" SQL at all? The statement isn't dynamic; it's just parametrised. You're providing an example of injection yes, but the reason it's can be injected is because you're using dynamic SQL for something that shouldn't be.

    Take the below example:

    CREATE PROC MyProc @Name varchar(50) AS 
    BEGIN

    DECLARE @SQL nvarchar(50);
    SET @SQL = N'SELECT * FROM MyTable WHERE [Name] = @Name;';

    EXEC sp_executesql @SQL, N'@name varchar(50)', @Name;
    END;

    There's no need for sp_executesql, the whole thing can just be replaced with the below:

    CREATE PROC MyProc @Name varchar(50) AS 
    BEGIN

    SELECT * FROM MyTable WHERE [Name] = @Name;

    END;

    You also discuss injection in the first video, and then throw the entire idea to the wind in the second, with statements like:

    SET @SQL = N'SELECT * FROM N' + @MyTable + N' WHERE MyColumn = @Value;';

    EXEC sp_executesql @SQL, N'@Value int', @Value;

    You have a huge injection issue there. There's a lot to be improved here, and I'm afraid.

    Finally a WHILE to do a dynamic Pivot? Really? A WHILE is an awful way to build those statements. Use a proper data set method by using STRING_AGG, FOR XML PATH or a self referencing variable.

    Also, and this is more of a nit pick, but the embedded videos don't start at the start of the video. For example, the first one starts 51 seconds in (your can see this, as the URL is in the mark up is embed/jS3kpNavKM8?start=51&feature=oembed , emphasis on start=51).

    A bit of shameless self promotion, but I cover a lot of the Don'ts you do in this article in my own: Dos and Don'ts of Dynamic SQL. I suggest having a read; especially on QUOTENAME (which I have a separate article on as well if you're interested). I'm afraid, however, I don't recommend this article to beginners, as they'll learn bad practices right out from the start. They should really be learning the right things to do at the beginning, and learning about why the wrong things are wrong.

    • This reply was modified 2 months, 1 week ago by  Thom A.

    Thom~

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

  • dbishop

    Mr or Mrs. 500

    Points: 565

    OMG (and I NEVER use OMG), are you kidding. I took a nap and don't think I missed anything. I watched for 2 minutes as computer-generated code was typed on the screen and elevator music played in the background. I'm not a beginner and I've used dynamic SQL and like browsing things like this to see if I can pick up something but all I got out of the first 5 minutes of this (that's about all I could take) was bored.

    Surely you can do better than this!

  • Jonathan AC Roberts

    SSCoach

    Points: 16882

    I only managed about 30 seconds before I had to stop it.

Viewing 6 posts - 1 through 6 (of 6 total)

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