Dynamic SQL for beginners in 33 minutes

Question

Dynamic SQL for beginners in 33 minutes

arthur

SSC Enthusiast

Points: 140
More actions
August 12, 2019 at 12:00 am

#3665662

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

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

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

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

petehughes247 SSC Enthusiast Points: 100 More actions · Answer 2

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: 99126 More actions · Answer 3

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 4 years, 9 months ago by Thom A.

Thom~

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

dbishop Mr or Mrs. 500 Points: 565 More actions · Answer 4

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: 18271 More actions · Answer 5

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