Hey guys welcome back to this SQL tutorial course. In the session we will
learn about stored procedures in SQL. So a stored procedure is basically a
prepared SQL code which we can save so that the code can be used again so if
you have an SQL query that we write over and over again we can save it as a
stored procedure and then just call it whenever we want. Now let's have a look
at the syntax of a stored procedure. So we'll give the keywords create procedure
and then give the procedure name, after that we'll give the keyword as and then
give the SQL statement for which we need to create the procedure and we'll end
the procedure with the GO keyword. Now after we create a procedure we need to
execute it. So this is the syntax to execute the procedure. We'll type exec
and then give the name of the procedure which is to be executed. So let's go to
SQL server and work with some procedures. So now I want to create a
procedure which would help me to easily access this e age column from the employee
table so I'll type create procedure and then I give the name of
the procedure which would be employee age. I'll type as and then give
the SQL statement which would help me to access this e_age column that would be
select e_age from the name of the table is employee and then I'll give the GO
keyword now I'll hit execute right, so we see that we have successfully created
the employee age procedure now I will use the exec keyword and then call
this procedure. So the name of the procedure is employee age I'll select
this line, i will hit execute right so with this employee age procedure we
can easily access the e_age column from the employee table now similarly I'll
create another procedure which would help me to access all of the records
from the employee table. Create procedure and I'll name this
procedure as employee details. I'll follow it up with the as keyword and
then give the SQL statement which would help me to access all of the records
from the employee table select start from employee and again
I'll finish this procedure off with the GO keyword. I will select all of this
i will hit execute. So again we have successfully created the employee details procedure
now to call this procedure I'll use the exec command and then pass in the name
of the procedure which is employee details so I will select this, I'll hit
execute right so with the help of employee details procedure we can easily access
all of the columns and all of the records from this employee table. Now we
can also pass parameters to the procedure. So the syntax for this is a
slightly different. Here after giving the procedure name we follow it up with the
list of parameters and their corresponding data types. So let's create
a procedure with a parameter now. So now I'll create a procedure which would help
me to access the male employees and the female employees separately from this
employee table. Create procedure and I'll name this procedure
to be employee gender after this I will pass in the parameter. So the name of the
parameter would be gender and that is of variable length character with a maximum
length of 20 I will give the as keyword after this
and then pass in the statements which would help me to individually access the
male employees and the female employees. Select start from the name of the table
is employee I'll follow it up with the where clause and give the condition but
this time the condition would be e_gender is equal to at the rate gender
let's understand this properly so here e_gender is basically this column and
we are passing in the value of this parameter to this column so let's say
when we call this procedure and the value is female then we'll get all of
the female employees and similarly if the value is male then we'll get all of
the male employees. Now let me finish off this procedure with the GO keyword
right so we have successfully created this employee gender procedure. Now let
me go ahead and execute this procedure so I will type exec and then give the
name of the procedure which would be employee gender and now let's say I want
all of the male employees so I will give in the parameter name at the rate
gender and I will give in the value which would be equal to me so let me execute
this now right so I've got all of the male employees from the employee table
similarly if I wanted all of the female employees from the employee table this
would be the command for that, execute I will give in the name of the procedure
which would be employee gender, I will give the name of the parameter which
would be @gender and then pass in the value so this time I would want all
of the female employees so the value would be female and select this i ll hit
execute right so this time we've got all of the female employees from the
employee table so this is how we can work with stored procedures either with
parameters or without parameters in SQL. So thanks for attending the session
let's meet in the next class.