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!
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]
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’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 🙏🏽
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.
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
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.
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 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.
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.
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. :(
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 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
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
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??
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.
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!
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.
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.
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
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 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, ?????
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
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
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! :)
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.
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.
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
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. 🙏
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?
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 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?
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 ?
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.
I'm looking to optimize where clause using udt and if I can switch from using (not exists(select 1 from @param) or exists (select 1 from @param where value = mainqueryvalue)). Any suggestions?
I dont understand the part where you say “you don’t have to put the @Jobtitle right here. You can put it wherever you want”, like where? How would that look?
When i modified mine and execute the Temp_Employee i didnt get the error message and im a dummy all i need to do is execute the modified procedure so and now its working
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
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
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.
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
Best UA-cam channel for Data Analysts!!❤️🔥
😁😁
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
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
Thank you very much Alex. Your videos are adding much values in my learnings.
Thank you I’ve been confused on this one for ages
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....
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.
Yes Alex, Nice work there!
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?
You are very 'on point'. Very precise
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 👍
Love the database from The Office
When I try to insert value into #temp_employee it says invalid object name 2:33 .
I subscribe straight along the way....
Your tutorial is awesome
Thank you for making this video.
I understood what SP is in this tutorial.
Just little bit.
you explain this so easily. appreciate this!
Superb explanation and content, keep it up Alex!
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!
Please make video regarding difference between Stored procedures and views.
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
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!
It's funny how most SQL tutorials I've encountered use the employee names from The Office haha
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.
Seems more like how we write a function in python. Thanks for the video Alex.
Thank you Alex, it was really a good content.
Stored Procedures are somewhat complex. In what context would we need to use it?
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.
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?
Hey Alex, thank you for providing quality learning content for the community.
You're more than welcome! Thanks for watching!
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. :(
Thank you very much Alex
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?
Hello! When I create 'Stored Procedure', it is created in the Master Database, is there any way to stop this?
Thank you Alex... Could we get questions on SQL???
Thank you so much, this video was very helpful. I think Stanley feels a little young however. 10/10
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
Thanks buddy very informative
Do you have the data analyst courses in uedmy, please share it
Clear explanation 👏👏👏👏
It was nice video and easy to understand. Thank you so much
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 :)
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??
Super video ! Breve y muy práctico. Muy bien explicado. Muchas gracias !
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.
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!
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.
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.
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
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
excellent tutorial...
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, ?????
Thanks for this channel
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
Thanks. Nice explaination
Great video! Could you make one explaining more in deep the Parameters in SQL?
That will be very helpful Alex
how did you do the refreshing?
Awesome! Thank you!
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 😄
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
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 💚💚
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.
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.
Will you be doing Triggers at all?
You have to also notice potential parameter sniffing issues when you design your procedure.
Thank you Alex.
grate channel but am having trouble refreshing in stored procedures it very convincing
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
this lesson is abit hard but i will gett through 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. 🙏
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!
How could I do to alter the procedure using mysql? I didn't manage to adjust the syntax.
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?
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?
Really cool!
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?
Once modified, I execute the parameter query but I get an error that states that I 'Must declare scalar variable "@Salesman" ' . Can anybody help ?
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 ?
where can I find the #temp_employee table you created within the procedure? I could not find it anywhere
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
Why is it that my stored procedures didn't result in a "table" but it did show that the command was successful?
Any thoughts?
I'm looking to optimize where clause using udt and if I can switch from using (not exists(select 1 from @param) or exists (select 1 from @param where value = mainqueryvalue)). Any suggestions?
Hello Alex - I couldn’t modify the storage procedure because I couldn’t find it under programmability, though it executes just fine.
look inside the master folder
Programmability -> Stored Procedures -> Right click the database that you want to modify.
I dont understand the part where you say “you don’t have to put the @Jobtitle right here. You can put it wherever you want”, like where? How would that look?
deos anyone know why my procedure temp_employee appear in the master system database instead of in the SQLTutorial database?
Hello Mr.Alex,
Please make videos on projects that make job seekers portfolio strong.
Nice idea
When i modified mine and execute the Temp_Employee i didnt get the error message and im a dummy all i need to do is execute the modified procedure so and now its working
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