YT YouTube2Text

YouTube2Text → Transcripts → Stored procedures in sql server Part 18

Stored procedures in sql server Part 18 — Transcript

by kudvenkat · 3,183 words · 463 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00welcome to preum Technologies I am wenet
0:02this is part 18 of SQL server in this
0:05session we'll understand what a stor
0:07procedure is a simple stor procedure
0:10example creating a store procedure with
0:12parameters altering a stor procedure
0:15viewing the text of a stor procedure and
0:17finally we'll see how to drop a stored
0:20procedure a stored procedure is a group
0:23of transact SQL statements if you ever
0:26have a situation where you have to write
0:28the same query over and over again you
0:31can save that specific query as a stored
0:33procedure and call it just by its name
0:36let's understand what we mean by this
0:38with an
0:40example now I have this table called TBL
0:43employee which has got you know the ID
0:46name gender and Department ID columns
0:48let's say I want name and gender of an
0:52employee so we type in select name comma
0:55agenda from TBL employee so every time I
0:59want the the name and gender of an
1:01employee you know I have to write this
1:03query okay instead of that what we can
1:06actually do is wrap this query inside a
1:09stored procedure and call that stored
1:12procedure rather than you having to
1:14write this query again and again so how
1:16do we create a stor procedure to create
1:18a stor procedure we use create procedure
1:22command so create procedure and then you
1:26have to give this procedure a name okay
1:29so s P let us say get
1:33employees okay since this procedure is
1:35getting us you know the employee name
1:37and genda I'm giving it get employees
1:40and look at this in the name I have this
1:42letters SP a common naming convention
1:45for store procedures is that we usually
1:47prefix that with small letter S and
1:50small letter P okay indicating that you
1:52know just by looking at this name you
1:54can tell okay this is a stor procedure
1:57all right so create procedure procedure
2:00your name and then you will use
2:03as
2:05begin and
2:08end okay so the definition of your stor
2:11procedure goes uh between this begin and
2:14end okay so this is the body of your
2:17stor procedure okay now when I execute
2:19this
2:20command what's going to happen is a St
2:24procedure with this name gets created in
2:27this database which is nothing but
2:29sample that we have selected here okay
2:31now if you want to look at the stor
2:33procedure that you have just created you
2:34know you want to make sure whether if
2:36this procedure is actually created or
2:38not okay go into that database which is
2:41sample and then you have this folder
2:44called programmability expand that and
2:46you should see a folder called store
2:48procedures if you expand that you know
2:50we don't have it currently listed there
2:52just refresh that and you should see
2:55that store procedure which we have just
2:56created which is sp get employees okay
3:00so anytime you want the name and gender
3:02of an employee instead of writing this
3:05query again what you can actually do is
3:08execute the St procedure okay so if you
3:11want to execute you just need the name
3:13of the procedure so what happens when I
3:15execute the St procedure okay to execute
3:18the stor procedure you just highlight
3:20that and click execute and you get the
3:22name and gender you don't have to write
3:25that query anymore now you might be
3:26wondering it's a very simple query why
3:28don't I write that rather than having to
3:30create this procedure and then invoke it
3:33now this procedure may be simple in
3:35reality the procedures will be long you
3:37know there are very know St procedures
3:40with over 3,000 of lines for example
3:43okay and not only that there are several
3:45other benefits of using St procedures
3:47from security to network reducing
3:49Network traffic Etc we will be talking
3:52about the advantages of St procedures in
3:54a very great detail in a later
3:57session Okay so we use create procedure
4:01or create proc statement to create SP I
4:03mean you can either
4:05say create procedure or you can just say
4:08create proc for
4:11shortcut okay to create a store
4:13procedure uh we'll talk about the naming
4:16Convention of the stor procedures in
4:18just a bit okay and to execute the stor
4:21procedure we have seen how to execute
4:22that stor procedure you know you just
4:24copy that the name of the store
4:27procedure and you click this execute
4:29part button and what happens the SQL
4:31statement within that short procedure
4:33gets executed and it Returns the name
4:35and gender columns all right so that's
4:38where one way to execute it or you can
4:41use the exec
4:42keyword and then click this or you can
4:46use a full execute keyword and then
4:49again press F5 or you can also
4:52graphically execute the store procedure
4:54just right click on the stor procedure
4:56and select execute stor procedure and
4:58the moment you do that that it shows
5:00this window this procedure doesn't have
5:03any parameters otherwise you will have
5:05to supply the values for the parameters
5:07in just a bit we will see how to create
5:09a stor procedure that takes parameters
5:11now when I click okay it executes that
5:14stor procedure look at
5:16that all right so those are the
5:19different ways to execute St
5:25procedure now let us look at a simple
5:28example of how to create a stored
5:31procedure with
5:36parameters
5:38okay so let's go back to the table TBL
5:43employees all right now what I want to
5:45do is I I want to create a store
5:48procedure which takes two parameters
5:50maybe gender and the department ID okay
5:53for example if I pass gender as male and
5:57Department ID as one to your store
5:59procedure
6:00it should give me employees only within
6:03that gender and within that department
6:06okay so your store procedure needs to
6:08have these parameters okay so let's see
6:10how to do
6:12that so as usual to create a store
6:14procedure we use create procedure
6:16command so create procedure and give
6:19your procedure a meaningful name so
6:22SP get
6:25employees by
6:27gender and
6:31Department okay so I want the employees
6:33by gender and department now look at
6:35this now this procedure okay the user
6:39who invokes your St procedure is going
6:41to pass the store procedure the gender
6:44and the department ID so for them to be
6:47able to pass the values for gender and
6:49Department ID they should be parameters
6:51just like how functions have parameters
6:54in C or any other programming language
6:56St procedures can also have parameters
6:59okay so one is the gender parameter and
7:02if you look at gender it's text here so
7:05the data type is going to be nare care
7:07of maybe
7:0920 and Department ID Department ID is
7:13going to be integer so Department ID
7:17integer
7:18as begin and so the definition of your
7:22store procedure goes in between these
7:25lines okay so what do we want from the
7:28table we want the
7:30name and maybe just the gender and also
7:34the department
7:37ID from which table we want it from TBL
7:41employee table but we don't want all
7:44names genders and Department IDs we we
7:47want to filter them with what the user
7:50is going to pass in he's going to pass
7:52in the gender and Department ID so we
7:54are going to say the gender column here
7:58should be equal to whatever the user is
8:00passing in at gender and along the same
8:04lines and Department
8:08ID let's bring this to another line is
8:11equal to whatever the user is going to
8:13pass
8:15in okay so these parameters are like
8:19placeholders when users execute your
8:22store procedure they're going to pass in
8:24values for this gender and Department ID
8:27which will be replaced at execution time
8:29time okay so let's create the store
8:32procedure so to create that select the
8:34entire store procedure click execute
8:36button command completed successfully
8:38now if you refresh the stor procedures
8:41folder you should see SP get employees
8:44by agender and Department okay now to
8:48execute this store procedure I just need
8:51the name of the store procedure and look
8:53at this this store procedure now is
8:55expecting gender and Department ID
8:57parameters now look at this if I don't
8:59don't pass the parameters and if I try
9:01to execute that St procedure see
9:03highlight that and then press execute
9:07what's going to happen this procedure or
9:09function SP get employees by gender and
9:11Department expects parameter at gender
9:14which was not supplied and that makes
9:16sense it's expecting a gender parameter
9:19which is not supplied so we need to pass
9:21in the gender parameter since gender is
9:23of type nare I have to use single codes
9:26so I want the male employees Within
9:30you know Department ID 1 so Department
9:33ID is one so these are the two
9:35parameters that this to procedure
9:38expects and we need to pass them so when
9:40I press F5 now look at that I only get
9:44male employees within that you know
9:46Department ID one okay on the other hand
9:49if I want all the male employees in
9:50Department ID do two I can do
9:54so all right now when you have
9:58parameters like say you know what you're
10:00doing is you're just passing in the
10:02parameters so so so this male value will
10:06be taken into at gender parameter
10:08whereas this number one is passed into
10:11Department ID parameter okay now what
10:15happens if I put it the other way around
10:18I am passing one first okay so what's
10:22going to happen it will take this one
10:24into gender and one is an integer but
10:29gender is of type nare care and this one
10:31will be converted into nare care
10:33implicitly no problem but it comes to
10:36the second parameter male it it tries to
10:39take this into Department ID parameter
10:41and if you look at the data type of
10:43Department ID parameter it is integer
10:46okay so it tries to convert this string
10:50into integer and it fails and it throws
10:53an exception look at this if I try to
10:55execute this now I get an exception
10:58saying that error converting data type
11:01Vare to integer so it is trying to
11:04convert this male you know string of
11:07type and Vare into integer and we get
11:10that error okay so when you have
11:12multiple parameters that dist stor
11:14procedure is expecting and if you're
11:16passing just values the values order you
11:20know the order in which you pass them is
11:24important okay the first parameter will
11:27be used I mean the value here the first
11:30argument will be used with the first
11:32parameter and the second argument will
11:34be used with the second parameter okay
11:37that's why the order is important
11:40but if you use the parameter names like
11:44this let's say I want to pass one to at
11:48Department ID I can specify the name of
11:50the parameter like so and similarly I
11:53can specify the name of the parameter
11:55for
11:56gender so when I execute this now now I
11:59will have no issues because you're
12:01specifying the name of the parameter
12:03okay so SQL Server knows okay this one
12:06is meant you know to be the value for
12:09Department ID parameter and male is the
12:12value for gender parameter it's only
12:14when you don't specify the names of the
12:15parameter the order of the the order in
12:18which you pass the parameter is
12:19parameters is
12:22important all
12:26right and okay so we have seen how to
12:29create a simple store procedure and how
12:30to create a procedure with parameters as
12:32well and we have seen how to execute
12:34them as well okay now once you have the
12:37stored procedures let's say I have
12:39created two procedures until now SP get
12:41employees and SP get employees by gender
12:43and department now if I want to view the
12:46text of these store procedures what are
12:48the different ways that are available
12:50one way is to Simply right click on that
12:52store procedure script stor procedure as
12:55create to new query editor window this
12:59this you know generates the contents of
13:02that stor procedure look at this this is
13:04the stor procedure definition that we
13:05have created create procedure procedure
13:07name as begin end and then our query
13:12this is one way to look at the
13:14definition of a stored procedure and the
13:17other way is to use a system stored
13:20procedure you know these stored
13:22procedures that we have created here um
13:25are user defined store procedures these
13:27are not system store procedures now SQL
13:29Server you
13:31know has some system stor procedures
13:34defined okay and we use it for certain
13:37task for example I want to find the text
13:40of his store procedure how do I do that
13:42I can use a system store procedure
13:44called s Spore help text okay so look at
13:49this this is the name of the system St
13:51procedure SP underscore help text okay
13:56SP help text and then if I pass in the
13:58name name of the store procedure there
14:01SP get
14:03employees and then when I select them
14:06together and execute this look at this I
14:08get the text of my store procedure you
14:11can then copy that paste it here and see
14:14how does the implementation of the store
14:16procedure looks like okay so to view the
14:18definition of a stor procedure you can
14:21either right click on
14:24that script store procedure as create to
14:27new query editor window or you can use
14:30the system stored procedure Spore help
14:32text followed by the name of the store
14:35procedure which gives you the text of
14:37the store
14:38procedure okay now in this slide if you
14:41look at this you know whenever we name
14:44user defined store procedure Microsoft
14:47recommends not to use
14:50Spore you know prefix for user defined
14:53stored procedures because the reason is
14:56system stored procedures has that pre
14:59prefix okay now if you happen to use
15:02Spore prefix for your user defined store
15:06procedures there are two problems number
15:08one there will be an ambiguity between
15:10user defined stor procedures and system
15:12defined stor procedures just by looking
15:14at the name we cannot tell you know is
15:16this a user defined store procedure or
15:18system defined store procedure okay and
15:21another problem is with future releases
15:23of you know new SQL Server version there
15:26may be name conflicts you know if you
15:29create let's say Spore get date you know
15:32stored procedure and in the future
15:34release there is a system stored
15:36procedure which is you know similarly
15:38named Spore get date you know it's going
15:41to conflict with the user store
15:43procedure okay so to avoid problems like
15:46this it's always better not to prefix
15:49user defined St procedures with Spore
15:57prefix all right so to change the stor
16:00procedure now once we have created a
16:02stor procedure for example I have the
16:04store procedure SP get employees after I
16:06have created the St procedure let's say
16:08I want to change its implementation in
16:10some way how do I do that let's say at
16:14the moment when I execute this SP get
16:16employees to procedure I'm not getting
16:19the names sorted you
16:21know I mean the names are basically not
16:23sorted I want them to be sorted so how
16:26do I do that I will use the order by CL
16:28Clause so order by
16:31name okay so I'm changing the
16:33implementation of this toia now if I
16:35execute this create procedure once again
16:38look at this we get an error stating
16:39that there is already an object named s
16:41SP get employees why we we we already
16:44have that and we are trying to create
16:45that again with the same name so
16:47obviously we get that error Our
16:49intention here is to change the
16:51definition of that St procure not to
16:53create another store procedure so if you
16:55want to change the definition of the
16:57store procedure then you say alter
17:00procedure and I press F5 the store
17:02procedure gets changed now if we execute
17:04that we should have the name
17:06sorted okay so we use alter procedure
17:11statement to change the definition of a
17:12stor procedure and to delete the stored
17:14procedure we use drop procedure
17:17procedure name just like you know if you
17:19want to drop a table you will use drop
17:22table table name okay so similarly to
17:25drop a procedure you will say drop
17:29drop procedure and procedure name for
17:32example I want to drop or delete SP get
17:34employees you know I just pass it there
17:37I press F5 and then if I refresh
17:41the store procedures folder it's gone
17:44now it's deleted or what you can do
17:46alternatively right click on that and
17:48select
17:51delete okay now it's also possible to
17:54encrypt the text of the store procedure
17:56and it's very simple to do for example I
17:59have the store procedure
18:02now SP get employees
18:06by gender and Department look at this
18:09now this is not encrypted at the moment
18:12so when I use Spore help text and when I
18:15press F5 I able to get the text of that
18:19St
18:20procedure so that's how the St procedure
18:23is implemented now if I want to encrypt
18:27the contents the text of the store
18:29procedure I can do that how do I do that
18:32all you have to do is to use this switch
18:35this option with
18:38encryption okay and we want to alter
18:42that so I will say
18:44alter now when I press F5 look at this
18:47command completed successfully and now
18:49the moment I refresh this folder look at
18:52this we get a little lock symbol there
18:54indicating that this to procedure is now
18:56encrypted okay now if some somebody
18:59tries to you know um get the text of the
19:02encrypted St procedure we get a message
19:05saying that the text for the object is
19:08encrypted and and we cannot retrieve the
19:10text of that okay a and you get the same
19:13kind of message when you kind of use
19:16script stor procedure as create to new
19:19query editor window okay we get this
19:22error box you know if you look at here
19:25it says the text is encrypted so once a
19:28St proced procedure is encrypted you
19:30cannot view the text of that store
19:32procedure but however if you want to
19:34delete the store procedure you can go
19:36ahead and delete it and know just right
19:38click and select delete it gets deleted
19:40but you cannot view the contents of a
19:43store procedure that is
19:46encrypted all right in the next session
19:49we will see how to create an invoke a St
19:51procedure with output parameters in this
19:53session we have seen how to create a sto
19:54procedure with input parameters in the
19:57next session we'll talk about creating
19:58store procedures with output
20:01parameters on this slide you can find
20:03resources for asp.net and cop interview
20:05questions that's it for today thank you
20:07for listening have a great day

About this transcript

This page contains the full transcript of Stored procedures in sql server Part 18 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 3,183 words across 463 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.