SQL Server Programming Part 13 - Dynamic SQL

[@navyahs4924]

Practical teaching has given a very good understanding of the concepts. The session covers all variations and contexts of dynamic SQL. Thank you for this.

[@WiseOwlTutorials]

You're welcome Navya, thank you for watching and taking the time to leave a comment!

[@zzLazaruszz]

Hi Andrew, just wanted to let you know how thankful I am that you made all this "out of the world" content available to us for free.

[@giorgimodebadze1869]

This is the best SQL Chane I have ever seen. Your video is really useful and helps me to understand everything about SQL. thank you!

[@WiseOwlTutorials]

Thanks Giorgi! Happy to hear you're enjoying the channel and thanks for watching!

[@TsiriniainaRakotonirina]

Oh Andrews, wow! This today's tutorial is a diamond my Man. 1. I always put a "+ N'" at the beginning and at the end of my Dynamic Sql. And it is a pain in the neck to maintain them. You talk me today that I just need to add N'' only when I have a parameter between the Text. You saved my life 2. I didn't know that you can directly use the sp_executesql with parameters. That one is purely a blessing Thank you so much! God bless you always for the free knowledge you are passing to us

[@WiseOwlTutorials]

Happy to hear that the video was useful for you, thank you for watching!

[@muhammadyahya3992]

Best SQL tutorials on KZbin

[@Wallee788]

Love how this person explain things!

[@GiuseppeSerraonline]

Hi, can I get some tips on how pass a "list" of Table/View in a Dynamic SQL string?

[@krismaly]

I watched this video again and I love this video. Thanks

[@WithASideOfFries]

So well explained. Great job.

[@WiseOwlTutorials]

Thanks, glad you found it useful!

[@alanelger6851]

Many thanks, your tutorials take a lot of beating

[@prasanthgmgks]

Genius. So easy to understand and very good examples. Thanks for the video

[@hkaui8875]

Brilliant video! Was really struggling with my book's explanation of this. This really cleared things up for me and actually explained what the concatenation was for!!! (Which my book didn't!)

[@simonm1229]

Thank you so much, this video helped me to solve work related issues!!!!

[@joshuaharvey9271]

Pretty sure at 19:20 the reason he isn't using CAST or CONVERT on the YearList is b/c of the commas. That may be obvious upon reading this but at first I didn't understand why that was the case so just clarifying for other's purposes.

[@jancrisrramos5843]

Hi Sir, it's a good tutorial for everyone, Specially for the Beginners :) thank you so much!

[@BijouBakson]

Basically what you get here is a course on Dynamic SQL, and Inject, and some practice on concatenation stored procedure.

[@CenterpointConnect]

As always, great overview.

[@mr.prasadyadav]

Thank you Nice Lecture On Dynamic SQL

[@alirezabarzinzanganeh4707]

Great job in all parts

[@khanst3r]

thanks, was waiting for this tutorial!

[@krismaly]

I like this video and feature explained meaningfully. I love your video and teaching. I saw your focusing on the subject matter while teaching that is a must. It would be good to make intentional mistakes and overcoming the same by explaining may help some more knowledge to user it's my suggestion only. I hope you are going explain error handling in separate video. Error handling is very important in program and that is the hard part to learn. I wish you definitely make some EROR handling video.

[@arpitprashantbahety5953]

Could someone tell me how to include this sql code, or maybe import a sql file to a javascript file ?

[@hansfrohlich8801]

at the 'IN Operator' example wouldn't you receive a non-sarg statement due to the function on 'FilmReleaseDate' Column at the WHERE clause?

[@TheQciap]

Hi Andrew, another great video, it goes without saying. Actually quick question that came to my mind - what is in your opinion the best source to improve topics from each lessons from your videos (eg where I could find more excercises to verify my skills or more explanation )? Any idea?

[@spiralni]

yes sir! subscribed.

[@WiseOwlTutorials]

Thank you Cesar!

[@jacekk7819]

Nice video but concatenate string in 9:57 min currently doesn't work in SQL and to check that I copied full statement , there is no error (Commands completed successfully.) but no result as well

[@WiseOwlTutorials]

Hi Jacek! It still works, it just depends on which version of the Movies database you're using. That video uses an older version and you can find a link to download the script to create it in the description of the video. I hope it helps!

[@ipsitapani8641]

REALLY GREAT,THANKS ALOT,VERY NICE EXPLANATION.THANK U SO MUCH SIR.

[@ethelpalomino]

Thank you for your videos, they are great! For IN Statements it doesn't work for me. I want to pass the following SET @Season='AW18,SS19' into SET @SQLString=N'SELECT *FROM DR_COMPO_PROD_FOR_COLORS where parentseason in ('+@SeasonSt+')' but says as result Invalid column name, so I tried with SET @SQLString=N'SELECT *FROM DR_COMPO_PROD_FOR_COLORS where parentseason in ('''+@SeasonSt+''')', but the result is null. It seems It consider 'AW18,SS19' as a single value. Could you help please?

[@shivgan3g]

can i download the database to check on my system

[@marlenefout3576]

I appreciate you videos so much! I was lost in my Database Management class until I found your videos. Seriously lost. So thank you, thank you!!! I was hoping you had a video on catalog views, DMFs, DMVs, and indexing. Do you have these?

[@mamurillo84]

Hi, your videos are simply put "a pot of gold" for those of us who are delving into this sql server world. A question: I'm trying to insert some records from one current table to a historic table. I get this error "Incorrect syntax near word FROM". This is my code: ALTER PROCEDURE movetohistorico @tabladoc nvarchar(50), @anio char(4) AS BEGIN DECLARE @datosdoc nvarchar(50) = @tabladoc DECLARE @sqldoc nvarchar(MAX) = ' ' SET QUOTED_IDENTIFIER OFF; SET @sqldoc = 'INSERT INTO ' + @datosdoc + ' FROM ' + @datosdoc_a + ' WHERE estado = ''C'' and anio = ' + @anio + ''; exec(@sqldoc). SET QUOTED_IDENTIFIER ON; END Any commens will be appreciated. Thanks in advance.

[@kingingway3308]

Lovely! Thank you so very much

[@Unearthed13]

Hey Andrew :-) Another GREAT, AWESOME video ... just curious, what fonts are you using for the Text editor and Grid Results?? ... thank you so much for these vids :-) take care

[@Unearthed13]

***** Got it, thank you :-)

[@Ali-ds5iy]

hey where can i get the data that you seem to be using here?

[@WiseOwlTutorials]

Hi, there are links in the video description. I hope it helps!

[@rajkumarrajan8059]

why are we using NVARCHAR to declare the variables rather VARCHAR?

[@WiseOwlTutorials]

Mainly because sp_executesql expects a Unicode string learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver16 I hope that helps!

[@jthedwalker]

@9:00 Why wouldn't we just use DECLARE @Number NVARCHAR(10) instead of INT?

[@MrvladivostokMr]

Hi, I am still getting the same error after execution - Msg 137, Level 15, State 2, Line 1 Must declare the scalar variable "@SQL". (all variable was declared)

[@MrvladivostokMr]

that's the code DECLARE @Number INT DECLARE @Numberstring varchar(5) DECLARE @SQL NVARCHAR(MAX) SET @Number = 10 SET @Numberstring = CAST(@Number AS varchar(5)); SET @SQL = N' SELECT TOP '+@Numberstring+' FROM tblActor' exec sp_executesql @SQL

[@muhammadyahya3992]

DECLARE @Number INT DECLARE @Numberstring varchar(5) DECLARE @SQL NVARCHAR(MAX) SET @Number = 10 SET @Numberstring = CAST(@Number AS varchar(5)); SET @SQL = N' SELECT TOP '+@Numberstring+' * FROM tblActor' exec sp_executesql @SQL try this you are missing * before from tblActor

[@shailabhagwat6356]

How to prevent the SQL injection

[@mohamed-fatta]

thank you soo much i don't know how can i help u but, good work

[@thuckz]

How can I use this in VB.net?

[@MimiMimi-si1kv]

wow it is amazing i really really appreciate it thank you so much GOD bless you!!!!

[@ReelBigCohen]

So I see the numbers are set to INT(@Number) and then a little later down casted to a nvarchar(@NumberString). Why don't you just set the @Number to a nvarchar off the bat?

[@thiery572]

Very interesting.

[@shoueya]

His voice at 2x speed is like Andrew Ng's voice. :P

[@TNCYKR]

Super..thank you

[@PhaniChakravarthi]

Thank you for the very detailed articles and videos. I'm very much impressed with your content and these are very much useful to me. I am going through your other articles on SQL as well on your blog and below is one other solution may be with reference to the timeline around 8 min 40 sec, "You can use the CAST or CONVERT function to change one data type into another" (also mentioned in your article: www.wiseowl.co.uk/blog/s363/concatention-in-sql.htm). From knowledge obtained from your other articles, I think we can also use the QUOTENAME function which also prevents SQL injection from happening during concatenation and avoids any mess with placing single quotes for concatenation. Kindly confirm my understanding which may be useful to other readers as well.

[@WiseOwlTutorials]

Hi Phani, I'm repeating the answer I gave to your question on the Wise Owl website, I hope you don't mind! I suppose that you could use the QUOTENAME function for the part of the video that you reference as long as you use parentheses as the delimiter character. So, for example: SET @SQLString = 'SELECT TOP ' + QUOTENAME(@Number,'(') + ' * FROM Film ORDER BY ReleaseDate' Would return a string which appears like so: SELECT TOP (10) * FROM Film ORDER BY ReleaseDate This would indeed work if you passed it to the sp_executesql stored procedure. I may have missed the point of your question but I'm not sure that it's much better than using CAST or CONVERT in this case. Please let me know if I've misunderstood what you're saying! Thanks for the suggestion!

[@PhaniChakravarthi]

@@WiseOwlTutorials: Thank you for the response as always. Yes, I actually meant in current case QUOTENAME function would help and do not intend to say that its replacement for CAST / CONVERT. My bad, could not be specific as I was almost late midnight while typing my earlier comment. I understand that QUOTENAME is no replacement for CAST or CONVERT, but just wanted to share another technique which I learnt from your other tutorials (as many of us do not realize the beauty of excellent function QUOTENAME). May be while concatenation with dynamic SQL we could also use QUOTENAME with ' ' ' ' as well to avoid any mistakes with single quote placing. Here is the snippet of SP along with its usage: CREATE PROC sp_Top_N_FromTable ( @TableName NVARCHAR (128), @Number INT ) AS BEGIN DECLARE @SQLString NVARCHAR(MAX) SET @SQLString = N'SELECT TOP ' + QUOTENAME(@Number, '(') + ' * FROM ' + @TableName EXEC sp_executesql @SQLString END -------------------------------------------- EXEC sp_Top_N_FromTable 'tblActors', 10 EXEC sp_Top_N_FromTable 'tblFilms', 5

[@WiseOwlTutorials]

Thanks for the input, Phani, it's much appreciated!