Advanced SQL Tutorial | Stored Procedures + Use Cases

It's funny how most SQL tutorials I've encountered use the employee names from The Office haha

Thanks buddy very informative

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

deus te abençoe

this video was a little bit complicated for me :(

ok. so immediately after creating my stored procedure, i ran into the problem of it not being in the right database even though i did everything correctly and without syntax errors. if you also have this problem, i'm writing this comment to (hopefully) help. sorry, it'll be a long read.
so! like i said my stored procedure was not in my SQL Tutorial database as intended and after some searching through my own files still nothing. i read a comment saying that someone found their stored procedure in the master database, so i checked in there and there it was! so if you haven't been able to locate your own, yours may be there. after locating it, i looked up how to move the stored procedure to the right database and the steps i followed are here with my notes in brackets:

1. Go the server in Management Studio
2. Select the database, right click on it Go to Task.
3. Select generate scripts option under Task
4. and once its started select the desired stored procedures you want to copy [for this step i chose to just open the script in a new query window so i didn't have to download a file and work more quickly, but choose the method that is best for you)


now once you have the script of the stored procedure pulled up, look to the very top at (what i guess to be) the USE statement. the database in which the stored procedure is in will be in brackets. go to the USE and change the name of the database to the one in which you want to be the location of the stored procedure. once you change the database as needed, execute the whole thing and you should be good to go! be sure to check for the location and refresh as needed to make sure everything went smoothly before moving on. please note that these steps may not be helpful if you made a typo or anything, so check your query that created the stored procedure to see where you may have an issue. i suppose in the future, for creating objects such as stored procedures and anything else that needs a location specified, placing that USE statement when you first write the query may be the best course of action. i'm still new to this so there may be better ways to do this. at any rate, i hope this helps!! :)

edit: an additional note! i learned that to specify the database location, you must use GO directly after the USE statement, otherwise you'll get an error saying that the CREATE statement must be first (not totally sure what the GO does in this case, if anyone knows please share! :) ) so when you're creating your stored procedure for the temp table, write this right before your CREATE statement:
USE [ ]
GO

your desired database will go in the brackets of course :) then you should be good to go to continue writing your query!

edit 2: I double checked in my master database and saw i had a copy of my TEST stored procedure, so i just deleted them once i made sure i moved them to my desired database. so do with these copies as you see fit!

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 Alex. Your videos are literally the best. You came down to the beginners level and walked us from ground to top.

After creating the procedure, it doesn't show on stored procedures.

Why is it that my stored procedures didn't result in a "table" but it did show that the command was successful?
Any thoughts?

Hello. Can someone explain what '@' means? when, how and where Do we need to attached it? Thanks before.

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?

How could I do to alter the procedure using mysql? I didn't manage to adjust the syntax.

Hello! When I create 'Stored Procedure', it is created in the Master Database, is there any way to stop this?

Love the database from The Office

Awesome! Thank you!

Great tutorial. Practicing this on azure and I keep getting this error: 'CREATE PROCEDURE' must be the only statement in the batch. I don't know what it means. Can anyone help?

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.

Thanks. Nice explaination

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

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

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?

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 so much, this video was very helpful. I think Stanley feels a little young however. 10/10

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?

Thanks Alex

I am following this series from beginning and now this is the first video that i find difficult to understand 😐 still learning....

Jesus these things are a walk in the park in SQL and an absolute nightmare in Teradata. Theres pretty much zero syntax documentation.

Why are my stored procedures not showing, please help. :(

Huh, its literally just a function. Kinda figured there'd be more to these than that.

Im using Postgresql -

I can create the following function to retrieve all the data from the EmployeeDemographics table:

CREATE OR REPLACE FUNCTION test()
RETURNS TABLE (EmployeeID integer, FirstName varchar(50), LastName varchar(50), Age integer, Gender varchar(50))
AS $$
BEGIN
RETURN QUERY SELECT * FROM EmployeeDemographics;
END;
$$ LANGUAGE plpgsql;

But when I try to create a Stored procedure like this:

CREATE OR REPLACE PROCEDURE TEST()
RETURNS TABLE (EmployeeID integer, FirstName varchar(50), LastName varchar(50), Age integer, Gender varchar(50))
AS $$
BEGIN
RETURN QUERY SELECT * FROM EmployeeDemographics;
END;
$$ LANGUAGE plpgsql;

It fails to execute.

ChatGPT states since I'm using Postgres:

"The code for creating a stored procedure in PostgreSQL is correct, except that in PostgreSQL, stored procedures are called functions, not procedures."

What am I doing that is syntactically that is not allowing me to create a stored procedure in Postgres?

Hello Alex - I couldn’t modify the storage procedure because I couldn’t find it under programmability, though it executes just fine.

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.

Will you be doing Triggers at all?

For those using SQLite, this DBMS doesn’t support Stored Procedures. For people using MySQL, the Stored Procedures is quite different. Below is the way how I did the first and second examples. Finally, there is no way to modify the parameters or the body stored procedure in MySQL, so you must drop it and create another one using DROP PROCEDURES IF EXISTS

Example 1
DELIMITER &&
CREATE PROCEDURE TEST ()
BEGIN
SELECT * FROM Employee;
END &&
DELIMITER ;
CALL TEST;

Example 2
DELIMITER &&
CREATE PROCEDURE Test ()
BEGIN
DROP TABLE IF EXISTS Temp_Employee;
CREATE TEMPORARY TABLE Temp_Employee (JobTitle VARCHAR (50), EmployeeperJob VARCHAR (50), Avg_Age INT, Avg_Salary INT );
INSERT INTO Temp_Employee
SELECT JobTittle, COUNT(JobTittle), AVG (Age), AVG (Salary)
FROM Employee emp INNER JOIN EmployeeSalary sal ON emp.EmployeeID = sal.EmployeeID
GROUP BY JobTittle;
SELECT * FROM Temp_Employee;
END &&
DELIMITER ;
CALL Test;

EXEC is replaced by CALL here.

Stored Procedures are somewhat complex. In what context would we need to use it?

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!

Done

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?

Your Tutorials are amazing

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 the... this went a little faster than I expected. Lost track after you wanted to create the Temp_Employee 😅

Time to rewatch

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.

Thank you Alex.