Stored procedures in sql server Part 18 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

welcome to presume technologies I am
venker this is part 18 of sequel server
in this session we'll understand what a
stored procedure is a simple stored
procedure example creating a stored
procedure with parameters altering a
stored procedure viewing the text of a
stored procedure and finally we will see
how to drop a stored procedure a stored
procedure is a group of transaxial
statements if you ever have a situation
where you have to write the same query
over and over again you can save that
specific query as a stored procedure and
call it just by its name let's
understand what we mean by this with an
example now I have this table called TBL
employee which has got you know the ID
name gender and department ID columns
let's say I want name and gender of an
employee so we type in select name
agenda from TBL employee so every time I
want the name and gender of an employee
you know I have to write this query okay
instead of that what we can actually do
is wrap this query inside a stored
procedure and call that stored procedure
rather than you having to write this
query again and again so how do we
create a stored procedure to create a
stored procedure we use create procedure
command so create procedure and then you
have to give this procedure an name okay
so SP let us say get employees okay
since this procedure is getting us you
know the employee name and gender I'm
giving it get employees and look at this
in the name I have this letters SP a
common naming convention for store
procedures is that we usually prefix
that with small letter S and small
letter P
okay indicating that you know just by
looking at this name you can tell okay
this is a stored procedure alright
so create procedure procedure name and
then you will use as begin and and
okay so the definition of your stored
procedure goes between this begin and
end okay so this is the body of your
stored procedure okay now when I execute
this command what's going to happen is a
stored procedure with this name gets
created in this database which is
nothing but sample that we have selected
here okay now if you want to look at the
stored procedure that you have just
created you know you want to make sure
whether if this procedure is actually
created or not okay go into that
database which is sample and then you
have this folder called program ability
expand that and you should see a folder
called stored procedures if you expand
that you know we don't have it currently
listed there just refresh that and you
should see that stored procedure which
we have just created which is sp get
employees okay so anytime you want the
name and gender of an employee instead
of writing this query again what you can
actually do is execute the stored
procedure okay so if you want to execute
you just need the name of the procedure
so what happens when I execute the
stored procedure okay to execute the
stored procedure you just highlight that
and click execute and you get the name
and gender you don't have to write that
query any more now you might be
wondering it's a very simple query why
don't I write that rather than having to
create this procedure and then invoke it
now this procedure may be simple in
reality the procedures will be long you
know there are very no stored procedures
with over three thousands of lines for
example okay and not only that there are
several other benefits of using stored
procedures from security to network
reducing network traffic etc we will be
talking about the advantages of stored
procedures in a very great detail in a
later session ok so we use create
procedure or create proc statement to
create sp
I mean you can either say create
procedure or you can just say create
proc for shortcut ok to create a stored
procedure we will talk about the naming
convention of the stored procedures in
just a bit
okay and to execute the stored procedure
we have seen how to execute that stored
procedure you know you just copy that
the name of the stored procedure and you
click this execute button and what
happens the sequence statement within
that short procedure gets executed and
it returns the name and gender columns
all right so that's one way to execute
it or you can use the exact keyword and
then click this or you can use a full
execute keyword and then again plus f5
or you can also graphically execute the
stored procedure just right-click on the
stored procedure and select execute
stored procedure and the moment you do
that it shows this vendor this procedure
doesn't have any parameters otherwise
you will have to supply the values for
the parameters in just a bit we will see
how to create a stored procedure that
takes parameters now when I click OK it
executes that stored procedure look at
that alright so those are the different
ways to execute stored procedure now let
us look at a simple example of how to
create a stored procedure with
parameters okay so let's go back to that
table TBL employees all right
now what I want to do is I I want to
create a stored procedure which takes
two parameters maybe gender and the
department ID okay for example if I pass
gender as male and Department IDs one
tier stored procedure it should give me
employees only within that gender and
within that department okay so your
store procedure needs to have these
parameters okay so let's see how to do
that so as usual to create a stored
procedure we use create procedure
command so create procedure and give
your procedure a meaningful name so SP
get employees by gender and department
okay so I want the employees by gender
and Department now look at this now this
procedure okay the user who invokes your
stored procedure is going to pass the
stored procedure the gender and the
department ID so for them to be able to
pass the values for gender and
department ID they should be parameters
just like how functions have parameters
in c-sharp or any other programming
language stood procedures can also have
parameters okay
so one is the gender parameter and if
you look at gender it's text here so the
data type is going to be n where care of
maybe 20 and department ID Department ID
is going to be integers or Department ID
integer as begin and so the definition
of your stored procedure goes in between
these lines okay so what do we want from
the table we want the name and maybe
just the gender and also the department
ID from which table we wanted from TBL
employee table but we don't want all
names genders and department IDs we want
to filter them with what the user is
going to pass in is going to pass in the
gender and department ID so we are going
to say the gender column here should be
equal to whatever the user is passing in
at gender and along the same lines and
department ID let's bring this to
another line is equal to whatever the
user is going to pass in okay so these
parameters are like placeholders when
users execute your stored procedure
they're going to pass in values for this
gender and department ID which will be
replaced at execution time okay
so let's create the store procedure so
to create that select the entire stored
procedure click execute button command
completed successfully now if you
refresh the stored procedures folder you
should see SP get employees
Genda and department okay now to execute
the stored procedure I just need the
name of the stored procedure and look at
this this stored procedure now is
expecting gender and department ID
parameters now look at this if I don't
pass the parameters and if I did try to
execute that stored procedure see
highlight that and then plus execute
what's going to happen this procedure or
function SP get employees by gender and
department expects parameter add gender
which was not supplied and that makes
sense it's expecting a gender parameter
which is not supplied so we need to pass
in the gender parameter since gender is
of type and we're care I have to use
single quotes so I want the male
employees within you know department ID
1 so department ID is 1 so these are the
two parameters that this stored
procedure expects and we need to pass
them so when I press f5 now look at that
I only get male employees within that
you know department ID 1 okay on the
other hand if I want all the male
employees and department ID do a 2 I can
do so all right now when you have
parameters like this you know what
you're doing is you're just passing in
the parameters so so so this male value
will be taken into at gender parameter
where as this number 1 is passed into
department ID parameter okay now what
happens if I put it the other way on I
am passing one first okay so what's
going to happen it will take this one
into gender and one is an integer but
gender is of type and where cab and this
one will be converted into n we're care
implicitly no problem but it comes to
the second parameter male it tries to
take this into Department ID parameter
and if you look at the data type of
department ID parameter it is integer
okay so it tries to convert this string
into integer and it fails and it throws
an exception look at this if I try to
execute this now I get an exception
saying that error converting data type
where care to integer so it is trying to
convert this mail you know string of
type and where care into integer and we
get that error okay so when you have
multiple parameters that the stored
procedure is expecting and if you're
passing just values the values order you
know the order in which you pass them is
important okay the first parameter will
be used I mean the value here the first
argument will be used with the first
parameter and the second argument will
be used with the second parameter okay
that's why the order is important but if
you use the parameter names like this
let's say I want to pass one two at
Department ID I can specify the name of
the parameter like so and similarly I
can specify the name of the parameter
for gender so when I execute this now I
will have no issues because you are
specifying the name of the parameter
okay so sequence of a knows okay this
one is meant you know to be the value
for Department ID parameter and mail is
the value for gender parameter it's only
when you don't specify the names of the
parameter the order of the the order in
which you pass the parameter is
parameters is important alright
and okay so we have seen how to create a
simple stored procedure and how to
create a procedure with parameters as
well and we have seen how to execute
them as well okay now once you have the
stored procedures let's say I have
created two procedures until now SP get
employees and as we get employees by
gender and Department now if I want to
view the text of these two procedures
what are the different ways that are
available one way is to simply right
click on that stored procedure script
stored procedure as create two new query
editor window this you know generates
the contents of that stored procedure
look at this this is the stored
procedure definition that we have
created create procedure procedure named
as begin and
and then our query this is one way to
look at the definition of a stored
procedure and the other way is to use a
system stored procedure
you know these stored procedures that we
have created here are user-defined
stored procedures these are not system
stored procedures now sequel server you
know has some system stored procedures
defined okay and we use it for certain
tasks for example I want to find the
text of a stored procedure how do I do
that I can use a system stored procedure
called SP underscore health text okay so
look at this this is the name of the
system store procedure SP underscore
help text okay SP help text and then if
I pass in the name of the stored
procedure there SP get employees and
then when I select them together and
execute this look at this I get the text
of my stored procedure you can then copy
that paste it here and see how does the
implementation of the stored procedure
looks like okay so to view the
definition of a stored procedure you can
either right-click on that script stored
procedure as create two new query editor
window or you can use the system stored
procedure SP underscore health text
followed by the name of the stored
procedure which gives you the text of
the stored procedure okay now in this
slide if you look at this you know
whenever we name user-defined stored
procedure microsoft recommends not to
use SP underscore you know prefix for
user-defined stored procedures because
the reason is system stored procedures
has that prefix okay now if you happen
to use SP underscore prefix for your
user-defined stored procedures there are
two problems number one there will be an
ambiguity between user-defined stored
procedures and system defined stored
procedures just by looking at the name
we cannot tell you know is this a user
define stored procedure or system
defines stored procedure okay and
another problem is with future releases
of
you know new sequence of a version there
may be name conflicts you know if you
create let's say SP underscore ket date
just you know stored procedure and in
the future release there is a system
stored procedure which is you know
similarly named SP underscore get date
you know it's going to conflict with the
user stored procedure okay so to avoid
problems like this it's always better
not to prefix user-defined stored
procedures with SP underscore prefix
alright so to change the stored
procedure now once we have created a
stored procedure for example I have the
stored procedure SP get employees after
I have created the stored procedure
let's say I want to change its
implementation in some way how do I do
that let's say at the moment when I
execute this SP get employee stored
procedure I am NOT getting the names
sorted you know I mean the names are
basically not sorted I want them to be
sorted so how do I do that I will use
the order by Clause so order by name
okay so I am changing the implementation
of this tour procedure now if I execute
this create procedure once again look at
this we get an error stating that there
is already an object named SP get
employees why we already have that and
we are trying to create that again with
the same name so obviously we get that
error our intention here is to change
the definition of that stored procedure
not to create another stored procedure
so if you want to change the definition
of the stored procedure then you say
alter procedure and I press f5 the
stored procedure gets changed now if we
execute that we should have the name
sorted okay so we use all the procedures
statement to change the definition of a
stored procedure and to delete the
stored procedure we use drop procedure
procedure name just like you know if you
want to drop a table you will use drop
table table name okay so similarly to
drop a procedure you will say drop drop
procedure and procedure name for example
I want to drop or delete SP get
employees you know I just pass it there
I press f5 and then if i refresh the
stored procedures folder it's gone now
it's deleted or what you can do
alternately right-click on that and
select delete okay
now it's also possible to encrypt the
text of the stored procedure and it's
very simple to do for example I have the
stored procedure now as we get employees
by gender and Department look at this
now this is not encrypted at the moment
so when I use SP underscore health text
and when I press f5 I am able to get the
text of that stored procedure so that's
how the stored procedure is implemented
now if I want to encrypt the contents
the text of the stored procedure I can
do that how do I do that all you have to
do is to use this switch this option
with encryption okay and we want to
alter that so I will say alter now when
I press f5 look at this command
completed successfully and now the
moment i refresh this folder look at
this we get a little lock symbol there
indicating that this stored procedure is
now encrypted okay now if somebody tries
to you know get the text of the
encrypted stored procedure we get a
message saying that the text for the
object is encrypted and and we cannot
retrieve the text of that ok and you get
the same kind of message when you kind
of use script stored procedure as create
two new query editor window okay we get
this error box you know if you look at
here it says that text is encrypted so
once a stored procedure is encrypted you
cannot view the text of that stored
procedure but however if you want to
delete the stored procedure you can go
ahead and delete it and I'll just right
click and select delete it gets deleted
but you cannot view the contents of his
stored procedure that is encrypted all
right in the next session we will see
how to create an invocation
procedure with output parameters in this
session we have seen how to create a
stored procedure with input parameters
in the next session we'll talk about
creating stored procedures with output
parameters on this slide you can find
resources for asp.net in c-sharp
interview questions that's it for today
thank you for listening have a great day