YT YouTube2Text

YouTube2Text → Transcripts → Stored procedures with output parameters Part 19

Stored procedures with output parameters Part 19 — Transcript

by kudvenkat · 2,873 words · 413 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:00hello welcome to preim Technologies I'm
0:02wut this is part 19 of SQL
0:05server in this session we will learn
0:07creating and executing stor procedures
0:09with output parameters and learn about
0:12some of the useful system stor
0:14procedures now before contining with
0:16this session I strongly recommend to
0:18watch part 18 of this video
0:20series in part 18 of this video series
0:24we have seen how to create St procedures
0:26with input parameters in this session we
0:29will see how to create a stor procedure
0:31with output parameter to create a stor
0:34procedure with output parameter we use
0:36the keywords out or output if you look
0:39at the store procedure that we have here
0:41SP get employee count by jender okay
0:44this procedure has got two parameters at
0:48genda and at employee count and if you
0:50look at the differences between these
0:52two parameters at employee count has got
0:55output keyword whereas at gender doesn't
0:58have it so this output keyword indicates
1:02that this at employee count is an output
1:05parameter and at gender you know is an
1:08input parameter by default because it
1:10doesn't have the output keyword okay and
1:13if you look at the procedure definition
1:15itself you know all we are doing is we
1:18are you know we are passing in the
1:20gender for example if you pass in male
1:23you know as the agenda what this query
1:25is going to do is it's going to count
1:28you know from employee table how many
1:30males are there and then whatever count
1:33you you're getting you're using that
1:35count to initialize this output
1:38parameter okay now let's see how to
1:40actually you know create this St
1:44procedure so if you look at this we have
1:46this employee table and we have got some
1:48males and females now we are about to
1:51create a St procedure which gives us the
1:54employee count by gender and obviously
1:57to create a St procedure we use create
2:00procedure command so create procedure
2:03and then give the store procedure a
2:05meaningful name SP get
2:08employee count you know by gender okay
2:13we want to get the employee count by
2:15gender and this procedure obviously if
2:17you want the total count of employees by
2:19gender at the minimum you have to pass
2:22the gender information like do you want
2:25the male employee count or female
2:27employee count so at gender
2:31and this is going to be of type nare
2:34care let's say 20 is the size and then
2:38we're going to have another parameter
2:40called employee
2:42count okay and this is going to be of
2:46type integer and I want this to be of
2:48type output
2:53parameter as begin and so what do we
2:58want to do now we want to select the
3:01total number of employees you know for
3:03the past in agenda and to do that we
3:05will say select now if you look at this
3:08select count of star for example from
3:11TBL employee this will give us the total
3:15number of employees in the table but we
3:16don't want total number of employees we
3:19want the total number of you know male
3:22or female employees depending on what
3:25gets passed into the stored procedure so
3:28where gender is equal to now if you if I
3:30say gender is equal to male then it will
3:33give us the total number of male
3:34employees which is seven but we don't
3:36want to be hardcoding the gender like
3:39this instead we want to be using the
3:45parameter which the user is going to
3:47pass when he invokes the to procedure
3:50okay now when we execute this look at
3:52this we get that count and we are
3:54selecting it directly and and you know
3:56we get the result set but we don't want
3:58to do that instead we want to initialize
4:01this output parameter so that's why we
4:03will say
4:05select at employee count is equal to so
4:09what's going to happen whatever we get I
4:11mean whatever this count function
4:13returns instead of selecting it as a
4:15result set we are passing it to the
4:18output variable so the output variable
4:20gets uh I mean output parameter gets
4:23initialized okay and instead of saying
4:27star it's always good to use the column
4:30name there so let's use ID so that's
4:34it so let's create the store
4:37procedure so we have successfully
4:39created a St procedure with an output
4:41parameter all right now to execute the
4:45stor procedure with output parameter now
4:47we have created the stor procedure we
4:49want to execute that okay now usually we
4:52have seen how to execute a stored
4:54procedure with input parameters in the
4:56previous session okay to execute a
4:58stored procedure without parameter it's
5:01slightly different okay now you know
5:04that this to procedure is is accepting
5:07two parameters one is the gender
5:09parameter and the other one is the
5:11employee count gender is an input
5:14parameter employee count is an output
5:16parameter okay now obviously input
5:20parameter means you have to pass
5:22something to the store procedure whereas
5:24output parameter will return something
5:27back to you okay now when a store
5:30procedure return something back to you
5:33you want to hold it somewhere obviously
5:35in a variable that's why you first
5:38create a variable to receive the value
5:42okay so I'm creating a variable called
5:44at employee total and notice the data
5:47type of this variable should match the
5:49data type of your output parameter why
5:53because essentially we get a value of
5:56this data type back so we want a
5:58variable you know basically to hold the
6:01value that is going to come out of the
6:03store procedure so that's why we are
6:05creating a variable of integer data type
6:08okay and then when we actually execute
6:09the stor procedure look at this we are
6:11executing the store procedure here we
6:13pass the value for gender which is
6:15nothing but the male and then what is
6:18this query going to do it's going to
6:19count the number of males and then it's
6:22going to initialize you know this output
6:25parameter okay so we are passing in a
6:29variable which is going to receive that
6:32count in this case at employee total
6:35receives the value and then finally we
6:38print that value so let's see how to
6:40execute
6:41this okay so obviously to execute this
6:44to procedure okay first we need to
6:47declare a variable let's say at total
6:50count maybe of type integer so the data
6:54type of this variable should match the
6:57data type of the parameter the output
6:59parameter
7:00okay and then what you're doing is
7:03you're executing the stor procedure
7:06which stor procedure is that SP employee
7:09get count by gender and then when you
7:12execute look at this when you execute
7:14that stor procedure obviously you have
7:17to pass in for you know which gender do
7:21you want to count the number of
7:22employees male or female you have to
7:24pass that input value for that input
7:26parameter but whereas for the output
7:28parameter you don't have to pass it a
7:29value you will get a value back
7:33so here we want to check the count of
7:36male employees and then we pass this
7:40variable to receive the value back okay
7:45now when you pass that you need to
7:47specify the output keyword otherwise it
7:50will be null okay so that's why to
7:53indicate to the store procedure okay
7:55this at total count is an output
7:58parameter you have have to specify here
8:01that you can either say out or output it
8:03doesn't really matter okay and then
8:06finally what you can do is print the
8:08value or you can pass it to another St
8:12procedure or do anything you
8:14want okay so when we execute this we
8:17should get that value back which is
8:19seven here okay now look at this if I
8:21don't pass the output keyword there and
8:25if I try to execute that look at this it
8:28doesn't print anything
8:30because this is
8:32null okay it doesn't receive the value
8:35if you don't specify the output keyword
8:37and you know when you declare it if you
8:39if you haven't initialized the set total
8:42count to anything it will basically be
8:44null and you will end up printing now
8:46and to quickly check that you can check
8:48that here if you know maybe at total
8:54count is
8:57null maybe we want to say
9:02print at total
9:07count is null all we are doing is we are
9:10checking if it is null or not else if it
9:14is not null we want to print a message
9:17saying that it is not null print at
9:19total count is not null now if we
9:22execute that look at this we didn't
9:24specify the out keyword if we don't
9:26specify the out keyword this variable
9:28will not receive the value and since we
9:31haven't initialized this variable
9:33anywhere else it is going to stay null
9:36and if it is null it will print this
9:39line so let's execute this so when we
9:43execute these lines look at this at
9:45total count is null but on the other
9:47hand if you pass in something you know
9:50if you pass the out keyword what's going
9:52to happen this variable is going to be
9:55initialized with the count of employees
9:58of that gender okay so now when I
10:00execute this you should get the message
10:02saying that at total count is not now
10:05which means it has got some value all
10:08right so keep that in mind whenever you
10:11execute a stor procedure with output
10:12parameter you know for that output
10:15parameter you have to specify the out
10:17keyword otherwise it will not be
10:19initialized and it will always stay
10:22null all
10:24right okay so if you don't specify the
10:27output keyword when executing the store
10:28procedure the the at employee total
10:32variable will be null and we have just
10:33seen that okay now here you know we are
10:38passing look at this we we if you look
10:41at this to procedure the first parameter
10:43is gender parameter and the second
10:45parameter is the employee count so we
10:47are passing in in that order okay so the
10:51first value will be used by this
10:53parameter the second one will be used by
10:55this parameter okay now if you use the
10:59parameter names when you're passing
11:01values to the store procedure then the
11:03order doesn't really matter for example
11:06look at this let's say I want to pass my
11:08output parameter first how do I do that
11:11you have to use the parameter names okay
11:14let's get rid of this
11:18here okay so what's the parameter name
11:23it's at employee count so that at
11:26employee count is equal to this output
11:29parameter and you still should have the
11:31out keyword and then you can say at
11:37gender is equal to I want to pass mail
11:42okay now look at this I altered the
11:45order in which we are passing the
11:46parameter but parameters but since I
11:49specified the names of the parameter the
11:51stored procedure knows which value is
11:55for which parameter okay so obviously if
11:57I execute this now it should work
12:00without any issue so we get seven okay
12:03seven male
12:05employees all right there are some
12:07extremely useful system stor procedures
12:09which we can use for a variety of
12:11purposes for example here I have this
12:13Spore help procedure it's a system
12:16stored procedure and in the last session
12:18we have learned that you know any
12:20procedure that starts with SP uncore is
12:24usually a system St procedure and user
12:26defined St procedure shouldn't be having
12:28that name
12:29okay so if you see this Spore help
12:32system St procedure this procedure can
12:34be used to view the information about
12:37the store procedure like parameter names
12:39the data types Etc okay let's see this
12:42in action now if I want to find out more
12:45information about you know the store
12:47procedure that we have just created I
12:49can use Spore help take the store
12:52procedure paste there and when we press
12:55you know select those two and when I
12:57press execute you you should see okay
13:01name of the stored procedure and look at
13:03this the type is stored procedure this
13:05is a stored procedure and when we have
13:07created it the parameters it has got
13:10their data types Etc okay so you can use
13:13Spore help or if you don't want to use
13:16that system stor procedure you can you
13:18know go to programmability stor
13:20procedures folder refresh and you should
13:23see your store procedure there and if
13:24you expand that you should see the
13:26parameters that it is expecting
13:31all
13:35right okay now this Spore help you know
13:39you can use that with any database
13:42object for example tables views triggers
13:45etc for example look at this when I use
13:47this Spore help with this TBL employee
13:52what information do I get I get all the
13:55information about you know the different
13:57columns that are present in this table
13:59their data types Etc any indexes this
14:01tables this table has got any
14:04constraints this table has got basically
14:06you get the information about the object
14:10okay it could be table view stor
14:12procedure trigger Etc so Spore help work
14:16with any database object and if you
14:18don't want to use the system store
14:20procedure you can graphically look at
14:22the information about any database
14:23object again or what you can do is
14:26simply highlight that object and press
14:30alt F1 that's a keyboard shortcut and
14:33you you get the exact same information
14:35again okay so that's about Spore help
14:39system store procedure and in part 18 of
14:42this video series we have seen how you
14:44know what is the purpose of Spore help
14:46text if you want to view the definition
14:48of a stor procedure we use Spore help
14:51text system stor procedure okay
14:54similarly even if it is a stored
14:56procedure with output parameters you can
14:58still use Spore help text system stor
15:01procedure okay press F5 you should see
15:04the text of the system stor procedure
15:06and remember in part 18 we have seen how
15:08to encrypt a stor procedure if you have
15:11encrypted a system I mean a stor
15:13procedure you cannot view the text of
15:15that store
15:18procedure okay and finally this Spore
15:22depends which is really useful okay this
15:25stored procedure basically is is used to
15:28view the dependencies of the store
15:30procedure for example if a stor
15:31procedure depends on a table you know
15:34look at this this stor
15:38procedure depends so this St procedure
15:42where are we getting the information
15:43from this St procedure is getting the
15:45information from TBL employee table so
15:48this to procedure depends on this table
15:51okay so when I execute this so we get
15:55that information okay this to procedure
15:57is depending on these columns ID and
16:00gender present in these in this table
16:03TBL employee okay now how is this useful
16:06this is useful because let's say I want
16:08to drop TBL employee table okay if I
16:11drop when I say drop I'm I'm going to
16:14delete the table so if I delete TBL
16:16employee table and if I try to execute
16:19this to procedure what happens it will
16:21throw an error because this to procedure
16:24is using the table and if the table is
16:27not present obviously we get an error
16:30okay so I can quickly check okay are
16:32there any any dependencies and if there
16:36are dependencies I wouldn't delete this
16:38table okay so what happens if I delete
16:40it instead of deleting it let's rename
16:42it to something so TBL employee 11 I
16:46have just renamed it to that it's as
16:47good as deleting it and now if we
16:50execute the St procedure look at what's
16:52going to happen it will throw an error
16:54saying invalid object name TBL employee
16:57okay so you can quickly check okay what
16:59are the
17:01dependencies okay so since you want to
17:03delete this table you can check that
17:06table itself so you can use that TBL
17:10employee table here and when you press
17:13F5 look at this there is a stored
17:16procedure that is referring the TBL
17:19employee
17:20table okay so if you look at the
17:23messages here in the current database
17:25the specified object which is nothing
17:28but you know TBL employee the specified
17:32object is referenced by the following
17:34which is nothing but the system store
17:36proced here so it's saying us this St
17:38system I mean this stor procedure is
17:41dependent on this TBL employee table so
17:44deleting this table may affect the store
17:47procedure okay so we'll be cautious when
17:49we are about to do that so SP Spore
17:52depends view the dependencies of the
17:54store procedure this system stor
17:56procedure is very useful especially if
17:58you want to check if there are any store
18:00procedure that store procedures that are
18:02referencing a table that you are about
18:04to drop SP Spore depend can also be used
18:07with other database objects like tables
18:11Etc on this slide you can find resources
18:13for asp.net and cop interview questions
18:16that's it for today thank you for
18:17listening have a great day

About this transcript

This page contains the full transcript of Stored procedures with output parameters Part 19 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,873 words across 413 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.