I like how you run the code (before it is fully complete) to show the error messages you expect and then show how to fix said error messages. This really helps with understanding SQL and helps with understanding troubleshooting code in the future. Keep up the content, Alex!
Keep it in mind like a macro. Like with a macro you type a set of actions and save it under a name or button, stored procedures are like that but you save it only with name. When you want to do the same job again simply EXEC the name and it runs. The advanced step is to give it variables with @ and then it runs with the variables.
Cool. This lesson was much more harder than previous one. I'll check for myself what means "@" and other stuff.. Definitely I'll rewatch this video again. Thank you for this interesting lesson.
I was slightly struggling with it to follow along so here is the steps to actually get the same result: 1) You write a stored procedure: drop table if exists, create table, inset into and select - finish with GO (you can also wrap the whole thing in: BEGIN (all queries) END 2) Run what you wrote (all of it) 3) Exec [store procedure name] 4) Modify - add parameter 5) Run the whole thing again ( I mean the modify query) 6) Exec [store procedure name] [your parameter]
I’m actually taking a data analytics boot camp and they’ll show us a super simple example like your first one here and your videos are helping me keep my head above water. The way you explain is actually retainable. I think I’m going to start your course after this one so that I actually know what I’m doing 🙏🏽
Hi Alex, Thank you so much for sharing all this info and Kudos for owning now your own educational SQL platform. O hope this content doesn't disapper! If i might give you my 2 cents. i think parameter should have his own video or maybe making this one longer with a specified section for parameters. Bye!
Hi Alex, I am a huge fan of your channels. Recently I am looking for a job of data analyst. Could you please show us some behavior questions which might be asked frequently during the interview session. Again, thank you so much for your videos, it is really helpful to me.
You have not mentioned that after modifying the stored procedure we need to execute it in order to ensure the modified stored procedure is the one that runs when we do EXEC Test_Employee
Thank you very much for your excellent tutorial video clips. I regularly watch these clips, and they are incredibly beneficial. I am new to the store procedure. Any help creating an SQL server store procedure connecting two servers would be highly appreciated.
Hey Alex, Can you show us (a) how to compare two databases and their tables for gap analysis using Python - (b) and also how to compare text file data with table data using python assuming both have similar table structure... would appreciate your work and contribution for students and learners.
Hey Alex great video! I was following along and wondered what the differences are when using Postgresql as I tried to run the stored procedure and it didnt work. So how would I be able to create a procedure temp table in postgre or would I just have to create a function instead?
After modifying the temp_Employee i dont get an error it just shows me the info again. Am I missing something? I deleted and tried again multiple times. :(
Great course but having a little issue with the stored procedures in the object explorer- even after refreshing the created procedures are not listed- what is really werid is that they run ok when executed - has anyone else had this issue? i am working with SQL server management studiio 19.2. If you do have a solution, that would be awesome because all I can find online is "press refresh"
Thank you for the video, i have a question;;;in 2.54 when trying to create a stored procedure for temp_employees, you did select * from #temp_employee, are you saying to create the procedure, we have to first add the select statement to the query or are we creating the table first then inserting the select statement before we then create the stored procedure?? sorry im just a bit confused and appreciate your response
even i refresh the stored procedures there is no data appear in stored procedure where did the data go also i created another data temp _employee i cant see the data in stored procedures what shoud i do?
Hi Alex, hope you are having a good time I have a question for you, I have found very helpful the store procedures but in a data analyst role do you think it is also important to learn how to create triggers and functions? thank you
Depends on the role you have - in a previous role I was in I QA'd a lot of that stuff, but didn't write it a lot. Some won't ever touch it. Good to know in my opinion.
Thank you so much for the tutorial. I really appreciate it. I have a question. After I add a parameter to the procedure and execute the procedure again, the result is the same as the procedure without a parameter. No error or warning. It seems that my parameter didn't come into effect. What should I do? Thank you!
I will have to review and practice this one many times. I was asked in an interview if I wrote stored procedures. It was not something I did. Now I will practice based on what you have shown here. Also, how do you reference more than one parameter. Do you add another Where clause, use AND, ?????
Hey Alex !! Just wondering how insightful it would it be if you could post a video comparing the Google data Analytics Certification and the IBM data analyst certification
Thank you for a very informative series. Regarding the Stored Procedure modification, how do you save the modification? Doesn't look like it auto save, and the save options will save the SQL form and not the procedure itself.
Thank you for creating all these great tutorials. I have followed along with all of them and have learned a lot. However, in this video, when executing the following last line: EXEC Temp_Employee @JobTitle = 'Salesman' My results only showed the message (1 row affected)" without displaying the actual rows affected in the EXEC statement. Any thoughts as to why? Either way, your tutorials are excellent. They have propelled me further than the Google Analytics Certification program has in regards to SQL. Thank you once again for your invaluable resources!
This has been an amazing tool to help fortify what I have already learned. My question would be what would cause the opposite to happen? I pulled the code from your GitHub, ran what you ran, and made the same changes. I did this under SQLTutorial instead of Master. Would that cause this to happen?
Hi Alex, I am stacking in the initial creation of Procedure. Making error in the code when executing it. Its not validating to Employeedemography word, showing invalid
Hi everyone, I created the procedure, however it is not stored in stored procedures although I refreshed it again and again but it did not work. I searched online for solution but could not find it. I would appreciate any help here.
Hello,I was having the same problem and I couldn't understand how to fix it. But please, be attentive to every # or comma, since in my case i had written one # where it wasn't necessary and everything was going OK,it was being executed but I couldn't only see the new stored pocedure. In my case I had written: Create Procedure # Temp_DickaeRe AS Create Table #Temp_DickaeRe( Profesioni varchar (100), Nripunonjesve int, AVGMosha int , AVGPaga int ) Where in fact it should be : Create Procedure Temp_DickaeRe AS Create Table #Temp_DickaeRe( Profesioni varchar (100), Nripunonjesve int, AVGMosha int , AVGPaga int ) Without # in front of the name of the Temp_ sth. I hope it will work for u too now! :)
Hi Alex. Really wonderful content, I can't explain how this has helped me grasp the basic concepts. Please I keep getting an error on the step where we created a stored procedure, created a temp table and inserted data into the table. This is the error I'm receiving, """Msg 213, Level 16, State 1, Procedure Temp_Employee, Line 7 [Batch Start Line 20] Column name or number of supplied values does not match table definition.""" Please how do I rectify this? Thanks.
Hey Alex! there is any way to make other video with different example I know this is an advance video but I don't get it, is so confusing and frustrating. I feel all I did was copy and page and yeah I get the result but still lost with this, thank you
After running "EXEC Temp_Employee" , when I refresh the stored procedure, I do not get the "dbo.Temp_Employee " after refreshing. And when I select the entire code and execute, it shows " There is already an object named 'Temp_Employee' in the database but I can not see it anywhere. Any idea why it is happening??
Hello alex I'm facing a problem When i create a stored procedure, it does say commands completed successfully but when i refresh the stored procedures, it is still not showing And when i try to execute too, it gives error Can you help me with this error ?
Is semicolon required to seperate these two code blocks? I see examples with semicolons on the net. Also, on some websites, Go keyword is used at the end of stored procedure. It is also not a compulsory thing as I see.
what would happen if the parameter @jobtitle already has a default value like where jobtitle ='manager' then we excuuted the procedure like exce temp_employee @jobtitle = 'sales man' does it keep its old value or updates to the new one? thanks in advance
I typed your initial create procedures code exactly, but it gives me this message. I can't seem to figure out the issue. How would you resolve it? Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS Select* From employeedemographics' at line 2
Alex, I am using Postgres, and when I write this code: CREATE PROCEDURE test AS SELECT * FROM employeedemographics; I get this error: ERROR: syntax error at or near "AS" LINE 2: AS ^ SQL state: 42601 Character: 32 I've worked out other issues with the code and Postgres, but I can't fix this one.
Hye guys, great video as always. I just wondering, why i can't find my stored procedure although i already refresh the 'Stored Procedure ' folder many times. Hope someone can help me. 🙏
After you run the Temp_Employee stored procedure, where does the table #temp_employee get created? I can't see it under Tables in the db tree even after refreshing.
Awesome as always! Do you have an open email for questions? I just wanted to know if this side project of mine was decent for a portfolio project. I checked out your discord, but I didn't know if that was an appropriate place to ask or get responses as I don't know the background of anyone responding. Or if I asked in your discord would you answer?
Also a friend of mine who is at SWE at a big company said a lot of firms are moving away from stored procedures for hard coding. Is that true? I know you are planning on making some python courses, will that be covered? He doesn't explain things very clearly as he likes to sound super smart by being super technical when talking to me so I have really 0 idea why his claim might be true.
hey, thanks for sharing this video, I've did all the steps, but still after refreshing I can't able to find the Test in stored procedures, is there anything I can do to rectify.
Am I the only who could not INSERT INTO the temp employee table? I've watched the video over and over. It tells me Invalid Object name #Temp_employee when trying to run insert into, and after creating the procedure CREATE PROCE... As Create Table #Temp_employee ..... anybody?
I tried relating it to a function how it takes arguments and you can use argument in the function block so whatever you pass as a parameter while calling the fn will replace the values in the function.
I am unable to run the INSERT into #temp_employee chunk of code. I even copied and pasted from Alex' code. I receive this message - Invalid object name #temp_employee. Any ideas on what I could be doing wrong?
I like how you run the code (before it is fully complete) to show the error messages you expect and then show how to fix said error messages. This really helps with understanding SQL and helps with understanding troubleshooting code in the future. Keep up the content, Alex!
So glad to hear that it's helpful! Thanks for watching! :D
So far partition and stored procedures are the 2 videos that I found difficult. But I've learned a lot from this series. Thanks, Alex!
me too i didn't understand it
I thought I was the only one
same here and still try understand
Keep it in mind like a macro. Like with a macro you type a set of actions and save it under a name or button, stored procedures are like that but you save it only with name. When you want to do the same job again simply EXEC the name and it runs. The advanced step is to give it variables with @ and then it runs with the variables.
samee
Cool. This lesson was much more harder than previous one.
I'll check for myself what means "@" and other stuff.. Definitely I'll rewatch this video again.
Thank you for this interesting lesson.
I was slightly struggling with it to follow along so here is the steps to actually get the same result:
1) You write a stored procedure: drop table if exists, create table, inset into and select - finish with GO (you can also wrap the whole thing in: BEGIN (all queries) END
2) Run what you wrote (all of it)
3) Exec [store procedure name]
4) Modify - add parameter
5) Run the whole thing again ( I mean the modify query)
6) Exec [store procedure name] [your parameter]
Thank you! I was really stuck on this query, and your comment saved me from struggling too badly.
Thank You 😘
Thank you!!!! Was seriously stuck and frustrated.
Kgl
Best TH-cam channel for Data Analysts!!❤️🔥
😁😁
Thank you Alex. Your videos are literally the best. You came down to the beginners level and walked us from ground to top.
I’m actually taking a data analytics boot camp and they’ll show us a super simple example like your first one here and your videos are helping me keep my head above water. The way you explain is actually retainable. I think I’m going to start your course after this one so that I actually know what I’m doing 🙏🏽
This was more intense than the previous sessions.
I appreciate the fact that you used The Office names here
stored procedure is the most difficult one so far, but explained very well
Please keep these style of videos coming,very insightful!
So glad to hear that! Thanks for watching! :D
I am definitely rewatching over and over until I fully understand it
Great tutorial! I had never seen anyone using @ symbols in SQL code before so I wasn’t sure how it changed your code.
Thank you! Yeah this was a fairly simple example of using that - it can get quite complicated! A good place to start though 👍
Thank you very much Alex. Your videos are adding much values in my learnings.
Thank you Alex, your content is so useful. I was waiting for your next advanced series. Do you think you could show how to make advanced triggers?
Good idea! I'll look into that and see if I will add it to this series
this tutorial changed my mind am now on the real track for the progress thanks alex>>>
I am following this series from beginning and now this is the first video that i find difficult to understand 😐 still learning....
Great video! I've worked with SQL a few times but I still got a lot to learn. This was very helpful!
Great to hear!
Yes Alex, Nice work there!
I subscribe straight along the way....
Your tutorial is awesome
Hi Alex,
Thank you so much for sharing all this info and Kudos for owning now your own educational SQL platform. O hope this content doesn't disapper!
If i might give you my 2 cents. i think parameter should have his own video or maybe making this one longer with a specified section for parameters.
Bye!
Thank you I’ve been confused on this one for ages
Love the database from The Office
Thank you for making this video.
I understood what SP is in this tutorial.
Just little bit.
Superb explanation and content, keep it up Alex!
Hey Alex, thank you for providing quality learning content for the community.
You're more than welcome! Thanks for watching!
You are very 'on point'. Very precise
Great content Alex! Keep up the good work 👍
Hi Alex, I am a huge fan of your channels. Recently I am looking for a job of data analyst.
Could you please show us some behavior questions which might be asked frequently during the interview session.
Again, thank you so much for your videos, it is really helpful to me.
Your Tutorials are amazing
you explain this so easily. appreciate this!
Thank you Alex, it was really a good content.
It's funny how most SQL tutorials I've encountered use the employee names from The Office haha
Clear explanation 👏👏👏👏
Thank you so much, this video was very helpful. I think Stanley feels a little young however. 10/10
Seems more like how we write a function in python. Thanks for the video Alex.
What the... this went a little faster than I expected. Lost track after you wanted to create the Temp_Employee 😅
Time to rewatch
It took me a re-watch to understand. I just need to practice the alter procedures to understand it a little bit better 😄
It was nice video and easy to understand. Thank you so much
You have not mentioned that after modifying the stored procedure we need to execute it in order to ensure the modified stored procedure is the one that runs when we do EXEC Test_Employee
could you tell me how to do it?
Thank you very much Alex
Thank you very much for your excellent tutorial video clips. I regularly watch these clips, and they are incredibly beneficial. I am new to the store procedure. Any help creating an SQL server store procedure connecting two servers would be highly appreciated.
Super video ! Breve y muy práctico. Muy bien explicado. Muchas gracias !
excellent tutorial...
Please make video regarding difference between Stored procedures and views.
Thanks. Nice explaination
Thanks for this channel
Thanks buddy very informative
Awesome! Thank you!
Really cool!
Thank you Alex.
i really loved The Office names in DB hahahah
Hey Alex, Can you show us (a) how to compare two databases and their tables for gap analysis using Python -
(b) and also how to compare text file data with table data using python assuming both have similar table structure...
would appreciate your work and contribution for students and learners.
this lesson is abit hard but i will gett through it
Thanks a lot.
Thanks Alex
Thankyou🙏
Do you have the data analyst courses in uedmy, please share it
Hey Alex great video! I was following along and wondered what the differences are when using Postgresql as I tried to run the stored procedure and it didnt work. So how would I be able to create a procedure temp table in postgre or would I just have to create a function instead?
After modifying the temp_Employee i dont get an error it just shows me the info again. Am I missing something? I deleted and tried again multiple times. :(
Great course but having a little issue with the stored procedures in the object explorer- even after refreshing the created procedures are not listed- what is really werid is that they run ok when executed - has anyone else had this issue? i am working with SQL server management studiio 19.2.
If you do have a solution, that would be awesome because all I can find online is "press refresh"
I have the same problem too, I have refreshed so many times but still the same..have you found the solution to this?
@@nurizzatiumarzaman5056 yeah..make sure you open the program as an administrator..for that reason it seems to work...
@@nurizzatiumarzaman5056 same here
Thank you for the video, i have a question;;;in 2.54 when trying to create a stored procedure for temp_employees, you did select * from #temp_employee, are you saying to create the procedure, we have to first add the select statement to the query or are we creating the table first then inserting the select statement before we then create the stored procedure?? sorry im just a bit confused and appreciate your response
When I try to insert value into #temp_employee it says invalid object name 2:33 .
Thanks for sharing
Thank you 🙏
even i refresh the stored procedures there is no data appear in stored procedure where did the data go also i created another data temp _employee i cant see the data in stored procedures what shoud i do?
Thank you Alex... Could we get questions on SQL???
Hello! When I create 'Stored Procedure', it is created in the Master Database, is there any way to stop this?
Hello Mr.Alex,
Please make videos on projects that make job seekers portfolio strong.
Nice idea
grate channel but am having trouble refreshing in stored procedures it very convincing
Hi Alex, hope you are having a good time
I have a question for you, I have found very helpful the store procedures but in a data analyst role do you think it is also important to learn how to create triggers and functions? thank you
Depends on the role you have - in a previous role I was in I QA'd a lot of that stuff, but didn't write it a lot. Some won't ever touch it. Good to know in my opinion.
Thank you so much for the tutorial. I really appreciate it. I have a question. After I add a parameter to the procedure and execute the procedure again, the result is the same as the procedure without a parameter. No error or warning. It seems that my parameter didn't come into effect. What should I do? Thank you!
I will have to review and practice this one many times. I was asked in an interview if I wrote stored procedures. It was not something I did. Now I will practice based on what you have shown here. Also, how do you reference more than one parameter. Do you add another Where clause, use AND, ?????
Hey Alex !! Just wondering how insightful it would it be if you could post a video comparing the Google data Analytics Certification and the IBM data analyst certification
I plan on doing that!
@@AlexTheAnalyst yaay ! Thanks :)
Thank you for a very informative series. Regarding the Stored Procedure modification, how do you save the modification? Doesn't look like it auto save, and the save options will save the SQL form and not the procedure itself.
Thank you for creating all these great tutorials. I have followed along with all of them and have learned a lot. However, in this video, when executing the following last line:
EXEC Temp_Employee @JobTitle = 'Salesman'
My results only showed the message (1 row affected)" without displaying the actual rows affected in the EXEC statement. Any thoughts as to why?
Either way, your tutorials are excellent. They have propelled me further than the Google Analytics Certification program has in regards to SQL. Thank you once again for your invaluable resources!
Great video! Could you make one explaining more in deep the Parameters in SQL?
That will be very helpful Alex
This has been an amazing tool to help fortify what I have already learned. My question would be what would cause the opposite to happen? I pulled the code from your GitHub, ran what you ran, and made the same changes. I did this under SQLTutorial instead of Master. Would that cause this to happen?
Hi Alex, I am stacking in the initial creation of Procedure. Making error in the code when executing it.
Its not validating to Employeedemography word, showing invalid
Hi everyone,
I created the procedure, however it is not stored in stored procedures although I refreshed it again and again but it did not work. I searched online for solution but could not find it. I would appreciate any help here.
Hello,I was having the same problem and I couldn't understand how to fix it. But please, be attentive to every # or comma, since in my case i had written one # where it wasn't necessary and everything was going OK,it was being executed but I couldn't only see the new stored pocedure.
In my case I had written:
Create Procedure # Temp_DickaeRe
AS
Create Table #Temp_DickaeRe(
Profesioni varchar (100),
Nripunonjesve int,
AVGMosha int ,
AVGPaga int )
Where in fact it should be :
Create Procedure Temp_DickaeRe
AS
Create Table #Temp_DickaeRe(
Profesioni varchar (100),
Nripunonjesve int,
AVGMosha int ,
AVGPaga int )
Without # in front of the name of the Temp_ sth.
I hope it will work for u too now! :)
I faced the same thing but then I found out that the procedures were stored in master database ,maybe the same thing could be happening with you.
@@riddhitandel7471 THANKS FOR THE HEP IT WENT A LONG WAY 💚💚
Hi Alex. Really wonderful content, I can't explain how this has helped me grasp the basic concepts. Please I keep getting an error on the step where we created a stored procedure, created a temp table and inserted data into the table. This is the error I'm receiving, """Msg 213, Level 16, State 1, Procedure Temp_Employee, Line 7 [Batch Start Line 20]
Column name or number of supplied values does not match table definition.""" Please how do I rectify this? Thanks.
Hi Solomon. Did you end up finding a solution to this issue? Thanks
Might be due to pre-existing temporary table in the current session with the same name. You can just modify it.
Hey Alex! there is any way to make other video with different example I know this is an advance video but I don't get it, is so confusing and frustrating. I feel all I did was copy and page and yeah I get the result but still lost with this, thank you
After running "EXEC Temp_Employee" , when I refresh the stored procedure, I do not get the "dbo.Temp_Employee " after refreshing.
And when I select the entire code and execute, it shows " There is already an object named 'Temp_Employee' in the database but I can not see it anywhere.
Any idea why it is happening??
Hello alex
I'm facing a problem
When i create a stored procedure, it does say commands completed successfully but when i refresh the stored procedures, it is still not showing
And when i try to execute too, it gives error
Can you help me with this error ?
Is semicolon required to seperate these two code blocks? I see examples with semicolons on the net.
Also, on some websites, Go keyword is used at the end of stored procedure. It is also not a compulsory thing as I see.
Will you be doing Triggers at all?
what would happen if the parameter @jobtitle already has a default value like where jobtitle ='manager' then we excuuted the procedure like exce temp_employee @jobtitle = 'sales man' does it keep its old value or updates to the new one?
thanks in advance
I typed your initial create procedures code exactly, but it gives me this message. I can't seem to figure out the issue. How would you resolve it?
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS Select* From employeedemographics' at line 2
Alex, I am using Postgres, and when I write this code:
CREATE PROCEDURE test
AS
SELECT *
FROM employeedemographics;
I get this error:
ERROR: syntax error at or near "AS"
LINE 2: AS
^
SQL state: 42601
Character: 32
I've worked out other issues with the code and Postgres, but I can't fix this one.
Gary, I am working with postgres as well, did you ever figure out what to do on this?
were you able to find a solution to this am getting the same error
Stored Procedures are somewhat complex. In what context would we need to use it?
Hye guys, great video as always. I just wondering, why i can't find my stored procedure although i already refresh the 'Stored Procedure ' folder many times. Hope someone can help me. 🙏
i have no idea what you did to execute the stored procedure lol when i exec my procedure it shows that it is somewhere on my pc its just not showing
After you run the Temp_Employee stored procedure, where does the table #temp_employee get created? I can't see it under Tables in the db tree even after refreshing.
Awesome as always! Do you have an open email for questions? I just wanted to know if this side project of mine was decent for a portfolio project. I checked out your discord, but I didn't know if that was an appropriate place to ask or get responses as I don't know the background of anyone responding. Or if I asked in your discord would you answer?
Also a friend of mine who is at SWE at a big company said a lot of firms are moving away from stored procedures for hard coding. Is that true? I know you are planning on making some python courses, will that be covered? He doesn't explain things very clearly as he likes to sound super smart by being super technical when talking to me so I have really 0 idea why his claim might be true.
Why is it that my stored procedures didn't result in a "table" but it did show that the command was successful?
Any thoughts?
where can I find the #temp_employee table you created within the procedure? I could not find it anywhere
how did you do the refreshing?
hey, thanks for sharing this video, I've did all the steps, but still after refreshing I can't able to find the Test in stored procedures, is there anything I can do to rectify.
did you add them to the SQLTutorial database?
Am I the only who could not INSERT INTO the temp employee table? I've watched the video over and over. It tells me Invalid Object name #Temp_employee when trying to run insert into, and after creating the procedure CREATE PROCE... As Create Table #Temp_employee ..... anybody?
I tried relating it to a function how it takes arguments and you can use argument in the function block so whatever you pass as a parameter while calling the fn will replace the values in the function.
I am unable to run the INSERT into #temp_employee chunk of code. I even copied and pasted from Alex' code. I receive this message - Invalid object name #temp_employee. Any ideas on what I could be doing wrong?