SQL Server Programming Part 3 - Variables

[@shuvbhowmickbestin]

These lectures have the quality of a paid Udemy course, thanks to you sir for keeping it free on KZbin. You gained one more sub today.

[@WiseOwlTutorials]

You're very welcome! Thank you for the comment and for watching!

[@ddstutorials6048]

Within five minutes of this video and one sentence of your description sir, you have gained a fan and sub for life. Thank you for taking the time to explain this material in an understandable way.

[@Rational108]

This is exactly what I was looking for from many days..I wish, I would've come here earlier.. but I am sure it will take me beyond from here..love from India 🇮🇳🇮🇳

[@WiseOwlTutorials]

But at least you found us eventually! Happy to hear that you're finding the videos useful, thank you for watching!

[@blameonme1]

You are just too outstanding a teacher...thanks for the upload....learned and still learning alot from ur tutorials

[@jaquesaulait7679]

How refreshing - an excellent presentation! Clear enunciation, good explanations at a good pace, and a clear screen; not too much going on, objectives clear and attained. Nice one, Wowl..!

[@nikhilmunj108]

Your way of making understand things is just awesome.You make it feel like cakewalk!!!!

[@mmgebken]

Most excellent, I can understand his accent.

[@patrickwheeler7107]

Thanks for sharing. We are moving to SSRS 2016 from BO and the wheels are turning in regards to reports with parameters. In the past I have mainly used Views to encompass complex code, but for reporting these are going to work out just fine! Thanks a bunch!

[@navyahs4924]

Very useful session. Each sentence is informative. Thank you for the neat and organized content and delivery.

[@WiseOwlTutorials]

You're very welcome Navya, I'm happy to hear that you found it useful, thank you for watching!

[@alanazana6744]

Thanks for putting this tutorial on youtube. Its very helpful and exactly what I need.

[@jasonmalan4679]

You are a blessing, Thank you very much for your excellent and well thought out videos.

[@WiseOwlTutorials]

Thank you so much for the kind words and support Jason!

[@clintoncalhoun9838]

i've learned more from you than any tutorial in such quick time

[@shanehawkins4158]

I was struggling with this on my course and this explained it so much better , thanks

[@fadwa2413]

Thank You Andrew!! thanks for the upload....learned and still learning a lot from your tutorials

[@WiseOwlTutorials]

Thanks Fadwa!

[@arghyanandy6469]

Your videos are so helpful. Thank you for sharing your knowledge with us.

[@WiseOwlTutorials]

You're very welcome! Thanks for taking the time to leave a comment and for watching!

[@kylebecker5083]

This is a great explanation of variables. Thanks

[@WiseOwlTutorials]

You're welcome, Kyle! Thank you for the comment and for watching!

[@duythanhao9808]

you are outstanding teacher

[@WiseOwlTutorials]

Thanks so much!

[@ItsWithinYou]

Awesome! Thank YOU for sharing!

[@WiseOwlTutorials]

And thank you for watching!

[@1976JT]

THANKS! i am into this at this very moment in my school.

[@channelnaresh9374]

Thanks for teaching it is very useful

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@donjr3270]

Thank you sir for this tutorial.

[@WiseOwlTutorials]

You're welcome Don, thanks for watching!

[@lewiscifer999]

Great thanks! Nothing specific, I just needed a general overview of what they do, how they work etc. I've heard that it can come up in Data Analyst interview questions and I'd like to have as many areas covered as possible!

[@RandomAlexus]

Your channel is fantastic

[@anditruman6979]

Hi Andrew, thank you again for this series, too. I am a most avid student of yours. At about minute 15:55 the string is left with a comma and a blank at the end. I am kind of perfectionist and whenever I see s.th. that I would like to have different I try myself on it, which often times is difficult at first. So here I didn't like the comma and the blank left after the last name was added to @NameList. After struggling a little I found this solution and share it here (hoping that you do not mind): If (Right(@NameList,2)=', ') SELECT @NameList= Left(@NameList, LEN(@NameList)- 2) -- Check that there is no space left Print @NameList+'"' This doesn't belong yet into this lesson, I assume. But perhaps there is a solution that is integrated already into the original SELECT? Thank you, Andi

[@deepskothi]

Thanks for the tutorials.. Course is well structured and helped me learn SQL server quicker (I come from Oracle scripting background). Couple of questions. (1) I do not see ';' being used as a statement terminator as used in Oracle/DB2 programming. With or without ; as the statement terminator program executes normally. What is the recommended best practice (2) At 16:55 if select is used instead of print, the actor list is outputted as a single line with each actor separated by a space. why is it select and print produce different form of output

[@eshwarchow9]

thank you..Mr.Wise Owl.very useful video..

[@guhansrinivasan7545]

Good explaination bro.

[@WiseOwlTutorials]

Thanks!

[@guhansrinivasan7545]

@@WiseOwlTutorials How long it takes to understand the flow of the advanced topics. Because I am a fresher and working as an intern.

[@WiseOwlTutorials]

@@guhansrinivasan7545 There's no simple answer to that question! It depends on so many things. Just keep working at it, you'll get there!

[@guhansrinivasan7545]

Thank you brother. I get your point but I feel like whether my approach is correct to do that in a secured way or not. A lot of questions go in my mind, that's why I asked your advice.

[@JoeG2324]

great job! keep the videos on sql coming.

[@OM-mu1qt]

Thank you soo much!

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@setudutta3905]

Thank you very much

[@marcin2x4]

13:29 - If I remove TOP 1 instruction I get one row only. does lack of TOP instruction indicates to SQL Server to perform in on other record?

[@kamran_desu]

Just came across something that was puzzling me, but I just figured it out now, might be of use to others. Local variables are limited to the scope of the batch (GO) that they are declared in. Hence, if your variable is referenced outside the batch, you will get an error asking you to declare the variable. Was scratching my head for a while there.... XD

[@GaryHutsonVBA]

Cracking tutorial! Thanks Andrew! :)

[@eshwarchow9]

more eegarly looking for ur videos ..

[@julianstockli4634]

Is there a great difference between Oracle SQL and Microsoft SQL as far as the content of this videos is concerned?

[@julianstockli4634]

I havent found any tutorials of similar quality using Oracle SQL, so I was wondering if it makes sense to just use yours to learn SQL even though I am going to use Oracle SQL afterwards?

[@tamascsiszer7669]

Thank you. This is really good. :)

[@lewiscifer999]

Thanks a million that's brilliant! That makes so much sense! So it eliminates the split screen that you'd get if you ran two separate queries! And as long as each query follows the required criteria could make the statement as complex as we need? E.g joins etc

[@santhoshkunder6]

Great Video!.What is the difference between variable and Parameter?

[@AcquisitionCapitalist]

Really enjoying your videos thus far, Andrew. I'm following along and ran into a bit of confusion on the segment that runs from 11:45 to 14:15. I modified your code slightly, but it's not performing as expected. Instead of 'SELECT TOP 1', I tried "SELECT TOP 10', but the output only yields one result in the first results pane. I noticed that that result is the 10th result as ordered by ActorDOB (Paul Bettany instead of Simon Pegg), but I'm not understanding why it's limited to only that one. What am I missing? Thanks in advance for any wisdom you can impart. :)

[@thiery572]

Thanks.

[@mihaelacostea5783]

so what's the meaning of the 'Film' as Type in the query? What is Film between brackets, why is it red, and what's Type?

[@JoshuaDHarvey]

On the last example where you declare the variable a concatenated string plus the result of the select query - does this work bc the query iterates the results from the query statement so it essentially does 5 passes in this particular query?

[@WiseOwlTutorials]

Hi Joshua, it's not an iterator, no, the query doesn't get executed once for each value (although you could use a cursor to do that if you really wanted to!). If you've upgraded to SQL Server 2017 there's a new function called STRING_AGG which provides an easier way to do this docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 : DECLARE @NameList NVARCHAR(MAX) = '' SELECT @NameList = STRING_AGG(FirstName, ',') FROM Actor WHERE YEAR(DoB) = 1970 PRINT @NameList I hope that helps!

[@chebiano]

very useful video :) Thank You

[@kontrabando]

Thanks!

[@lewiscifer999]

Hi, Are there any vids on Unions?

[@thiery572]

When you said "r" it sounds funny, like "bwrr". 😊

[@cprogramer123]

Hello, thanks for the video, its very easy to follow, using this video i have created a procedure to which i pass a time stamp value, its gives me an error that i m trying to implicit convert varchar to date, can you help me out the right way to pass dates which are of the timestamp data type.

[@niteshsingh4377]

Can you tell me how have you changed the color for Varibles to green.

[@WiseOwlTutorials]

Hi Nitesh! In the version of SQL Server Management Studio used in the video (the one which came packaged with SQL Server 2012), those were the default colours. If you want to replicate it: 1) Choose Tools | Options... 2) Choose Environment | Fonts and Colors 3) In the "Show settings for:" drop down list choose Text Editor 4) In the "Display items:" list choose Keyword 5) Set the "Item foreground:" property to the colour of your choice I hope it helps!

[@derekjscott1]

that was - amazing

[@randlyce]

how come when i used PG admin which runs postgre sql this does not work? is this one of those different flavour syntax?

[@WiseOwlTutorials]

Basically, yes. This series of videos is specific to Microsoft SQL Server which uses Transact-SQL.

[@mihaelacostea5783]

I have this error as a result: Çonversion failed when converting date and/or time from character string' --> can you please suggest a correction?

[@eshwarchow9]

could u plz make a video on stored procedures with control flow statements, joins...after getting knowledge on joins i made one stored procedure with query containg 4 sql join statements,it doesn't work when i pass the parameters to stored procedure,but i got msg that stored procedure has created succesfully... is it possible to pass multiple values to stored procedure during execution by ussing in clause?

[@adi_u]

awesome!!!

[@emirhancelebi8316]

You've already selected ActorDOB from TOP 1 row of ActorTable so why would you still need to specify WHERE clause ?

[@dandaplays8808]

Hi, im not from uk, theres any option to buy your courses (e-learning courses) ??

[@emilyf2641]

Thanks for sharing. I am confused, what is a difference between parameters and variables in a stored procedure?

[@emilyf2641]

+WiseOwlTutorials Thank you again. your explanation was very helpful.

[@powerbiguy326]

@@emilyf2641 what he told you ???, as in SSIS variables are dynamic with expression and parameters are static with global scope , but in ssms , it is bit confusing to get the difference between these 2 concepts

[@rohansathasivam1266]

I love you!

[@sminkan1]

thanks for the advice,i must know that beacuse i dont know what im typing when i follow examples from videos :) Beacuse im preparing for 70-461, all your videos are on SQL 2012 and can I use them to study for the exam?

[@eshwarchow9]

let us example '@id' is a parameter in stored procedure which i have created succesfully...but i would like to get more than one id values during execution of stored procedure say exmple EXEC spfilmdata @id in (1,2,3)...here 1,2,3 ids how is it possible to pass multiple values through parameter to stored procedure..

[@shahimkhlaifat]

dear im trying to build a table name through varubale but it is not working: could you help declare @name nvarchar(50); declare @text nvarchar(50); declare @date nvarchar(50); declare @ex nvarchar(50); set @text= 'shahim ' set @name=@text+@date set @date= 'rasha' set @ex= 'select * into' + QUOTENAME(@name)+ 'from Sample.dbo.Active;'; EXECUTE SP_EXECUTESQL @ex

[@shahimkhlaifat]

***** thank you so much i have found this way and it is working fine, thank you again, really you are so helpful DECLARE @table_name varchar(max) SET @table_name= (SELECT 'TEST_' + DATENAME(YEAR,GETDATE()) + UPPER(DATENAME(MONTH,GETDATE())) ) --DROP THE TABLE IF IT ALREADY EXISTS IF EXISTS(SELECT name FROM sysobjects WHERE name =@table_name AND xtype = 'U') BEGIN EXEC('drop table ' + @table_name) END --CREATES TABLE FROM DYNAMIC VARIABLE AND INSERTS ROWS FROM ANOTHER TABLE EXEC('SELECT * INTO ' +@table_name+ ' FROM dbo.active')

[@JoeG2324]

can you do a video on dynamic sql?

[@sminkan1]

WiseOwlTutorials, i just start to learn SQL ... and im very confused :) first in which of your tutorials i can find about var char,integer ... what they mean for what to use it etc etc.

[@jayjayf9699]

Why did you use date time and not date?

[@tulinkilic5233]

Hi, First of all I would like to say thanx alot for this wonderful tutoring videos. I am having small problem. which I would like to apply what I learned from your videos. So, in this case I need some tables How can get the tabels. I got couple of your table what name is "movies and sheet6" from your other lesson. I got it from excell table. But I need to get other tables for sql tutoring videos. Such as tblDirector, tblActor etc... If you could help me I would be appreciated.

[@tulinkilic5233]

Hi, Thanks alot for your rapid answer. Although I have an other question. I have downloaded "Create Movies Database 2016" file there is no problem on that but ı could not find cast table in that DB what you use it at the end of "sql server programing part 6". Well I am so sory If I am being too much :( But I appreciated for all your help.

[@tulinkilic5233]

one more comment on that, the table names are different on that DB the one you are using on your tutorials as well as colum names :)

[@tulinkilic5233]

Hi, Its me again. I downloaded "pt7 Computed Columns" There are 2 folders start and end which one should ı open

[@tulinkilic5233]

PS: The ones I had dowload from sql server 2016 part 7 and part 8.

[@krismaly]

I liked @Namelist Thanks

[@Radioactive-Cactus]

Hi WiseOwl

[@WiseOwlTutorials]

Hi Dustin!

[@hadireg]

👍👍

[@sauravchugh1501]

your accent reminds me of hogwarts teachers, lol.

[@WiseOwlTutorials]

😀 hopefully this lesson is more interesting than potions

[@KitoKitko]

Thanks!