1 00:00.830 --> 00:04.520 So today we will see first the table variable. 2 00:05.600 --> 00:07.910 So actually it is very small topic. 3 00:07.910 --> 00:09.500 Let it cover first. 4 00:10.280 --> 00:18.080 The table variable is a special type of local variable which helps to store the data temporarily similarly 5 00:18.110 --> 00:20.540 to temp tables in SQL server. 6 00:22.250 --> 00:29.240 So we are using something concept called as temporary table or temp table. 7 00:29.240 --> 00:36.710 And this is another uh concept which is help to store the data into temporary. 8 00:36.950 --> 00:42.980 And that another concept is known as table variable now. 9 00:45.050 --> 00:49.850 So syntax is very syntax is little different. 10 00:49.970 --> 00:54.920 We have to declare then we have to give some random name. 11 00:54.920 --> 00:58.370 And that name we need to tell as a table. 12 00:58.370 --> 01:00.220 So this is this is a table variable. 13 01:00.250 --> 01:01.030 Table. 14 01:01.870 --> 01:03.940 Underscore where is a table variable. 15 01:03.940 --> 01:05.860 And inside that particular table. 16 01:06.250 --> 01:06.820 Table. 17 01:06.820 --> 01:10.930 We need to specify all the columns with their data type. 18 01:11.560 --> 01:13.780 All the columns with their data type. 19 01:13.780 --> 01:16.390 So if ten columns are there. 20 01:16.420 --> 01:20.500 Then we need to specify ten different columns with data type. 21 01:20.920 --> 01:24.100 And then we can perform some data manipulation. 22 01:24.250 --> 01:29.500 So we can insert update delete data inside table variable then. 23 01:29.530 --> 01:33.760 Alter truncate does not work on table variable but they. 24 01:33.790 --> 01:35.560 Work on temporary tables. 25 01:36.010 --> 01:39.970 So when we are creating the temporary table like this create. 26 01:42.070 --> 01:49.360 Create test then a integer b where care of ten. 27 01:50.620 --> 01:52.210 So this is a temporary table. 28 01:52.240 --> 01:52.510 Right. 29 01:52.510 --> 01:54.070 This is a temporary table. 30 01:55.660 --> 01:58.530 Create table This. 31 01:58.560 --> 02:02.880 Now we can we can create index on this particular table. 32 02:02.880 --> 02:09.570 We can also alter this particular table like alter this table name. 33 02:10.380 --> 02:21.030 Then we need to specify table right alter table then add c integer. 34 02:21.030 --> 02:22.290 So we are. 35 02:22.290 --> 02:25.050 When we do this it will add one more column. 36 02:25.140 --> 02:30.450 But we cannot do at the time of table variable we cannot alter table variable. 37 02:31.380 --> 02:32.070 Alter. 38 02:32.070 --> 02:36.780 Truncate does not work on table variable but they work on temp tables. 39 02:36.780 --> 02:43.080 Unlike temp tables, the table variable is accessible within the current page. 40 02:44.610 --> 02:53.460 Current page table variables are current batch only, so once this batch will be gone we can. 41 02:53.490 --> 03:00.050 I will let you know once We will see below example then. 42 03:00.080 --> 03:04.190 Table variables are useful when dealing with small amount of data. 43 03:04.400 --> 03:10.310 Otherwise SQL server temp table is useful with dealing with large amount of data. 44 03:10.310 --> 03:13.190 So this is also one more thing. 45 03:13.220 --> 03:13.580 Temp. 46 03:13.610 --> 03:22.070 Temp tables will be useful when we have large amount of data, and table variable will be used when 47 03:22.070 --> 03:26.150 we have small amount of data. 48 03:27.860 --> 03:29.990 Now let's see one example. 49 03:29.990 --> 03:32.210 So here we have declared this variable right. 50 03:32.240 --> 03:33.560 Declare a variable t. 51 03:34.670 --> 03:36.320 Declare at the rate t. 52 03:36.740 --> 03:39.350 Then we need to specify the column name. 53 03:39.350 --> 03:46.520 Whichever the column we want to use in our data manipulation that column we need to specify. 54 03:46.550 --> 03:51.710 Then we can insert into the table variable using insert into it at t. 55 03:51.740 --> 03:54.020 So this is my table variable. 56 03:54.050 --> 03:54.350 Right. 57 03:54.350 --> 04:00.380 We can insert the data into table variable, insert into a t, then select. 58 04:00.650 --> 04:06.050 We are selecting the data from this table with department equal to 30. 59 04:06.860 --> 04:13.280 So all these six record will be stored inside this particular table variable. 60 04:13.280 --> 04:16.370 So we can select the data at the same time. 61 04:18.440 --> 04:22.370 Using select from T but now one. 62 04:22.400 --> 04:25.010 Once we are using this go statement right. 63 04:25.040 --> 04:27.350 Once we are using this go statement. 64 04:28.790 --> 04:36.620 So after they go let me try to select from this table variable. 65 04:36.620 --> 04:44.330 We cannot select from this table variable because it is for the current batch only. 66 04:44.330 --> 04:45.920 This is my current batch. 67 04:46.040 --> 04:52.850 Batch will be start from the declare and once I go written the go, the batch will be over. 68 04:54.490 --> 04:58.570 But in temporary table we can we can select the data. 69 04:58.570 --> 05:01.720 But in the table variable we cannot select the data. 70 05:01.900 --> 05:09.190 We can select the data in the current batch only once the batch is finished we cannot select the data 71 05:09.190 --> 05:11.110 into table variable. 72 05:14.680 --> 05:17.380 So this is the concept of table variable. 73 05:17.410 --> 05:21.400 Now let's start with the function. 74 05:26.800 --> 05:29.050 Let's start with the function now. 75 05:29.230 --> 05:32.170 So UDF user defined function. 76 05:32.200 --> 05:38.830 So UDF is an executable database object that that contains SQL statements. 77 05:39.370 --> 05:44.020 So see over here UDF will be normally stored over here in the programmatically. 78 05:44.440 --> 05:49.870 And over here in the functions it will be stored table values functions. 79 05:50.170 --> 06:00.570 Scalar valued functions, and these are the main UDF scalar value and table valued. 80 06:00.690 --> 06:04.080 Now see there are two kind of functions. 81 06:04.080 --> 06:09.750 Are there SQL server built in functions are there which we have seen already right. 82 06:09.780 --> 06:10.080 Date. 83 06:10.080 --> 06:10.740 Part. 84 06:11.160 --> 06:11.550 Date. 85 06:11.580 --> 06:12.150 Difference. 86 06:12.150 --> 06:12.480 String. 87 06:12.480 --> 06:12.960 Function. 88 06:12.960 --> 06:13.410 Lower. 89 06:13.440 --> 06:14.160 Upper. 90 06:14.220 --> 06:18.240 All are built in functions and now user defined functions. 91 06:18.360 --> 06:26.400 So functions created by the user and these functions are saved inside the database which can reuse from 92 06:27.000 --> 06:28.770 reuse when required. 93 06:29.670 --> 06:35.730 So this user defined functions are usually stored in the database right. 94 06:35.760 --> 06:38.370 Now user defined functions are of two types. 95 06:38.400 --> 06:42.690 Scalar value functions and table valued functions. 96 06:43.260 --> 06:49.560 Now functions returning a scalar value that is known as a scalar valued function. 97 06:49.560 --> 06:52.130 We will see one example below. 98 06:53.270 --> 06:55.040 At that time you will be more clear. 99 06:55.040 --> 06:57.020 And what is a table valued function? 100 06:57.020 --> 06:57.560 Functions. 101 06:57.560 --> 07:03.260 Returning a table that is known as table valued functions. 102 07:03.920 --> 07:05.690 Now see a function. 103 07:05.690 --> 07:07.280 Always return a value. 104 07:07.310 --> 07:09.620 A function always return a value. 105 07:09.890 --> 07:12.740 Function cannot have output parameter. 106 07:13.070 --> 07:20.390 I think yesterday we have seen the store procedure will have an output parameter, but function cannot 107 07:20.390 --> 07:23.000 have a output parameter. 108 07:23.300 --> 07:27.860 Now we have to use the return statement to return value to calling program. 109 07:28.430 --> 07:35.960 Parameters passing by name is not allowed in function, so only parameter passing by position can be 110 07:35.960 --> 07:40.370 used while calling a stored function. 111 07:41.210 --> 07:49.250 Now, unlike stored procedure and UDF cannot make permanent changes to the database object in the database. 112 07:49.250 --> 07:56.170 For example, it cannot issue, insert, update, delete statement against table or use in the database. 113 07:57.640 --> 08:05.110 But now suppose if you want to do the insert, update and delete in the stored function at that time, 114 08:05.110 --> 08:06.910 we can use the table variable. 115 08:06.940 --> 08:14.800 Table variable can be created and we can issue, insert, update and delete against table variable that 116 08:14.800 --> 08:15.760 we have seen over here. 117 08:15.790 --> 08:18.010 Right that we have seen over here. 118 08:18.340 --> 08:22.330 We have done the insert over here in the table variable. 119 08:24.280 --> 08:32.440 Now unlike other database object we must specify the name of the schema when invoking the new UDF. 120 08:34.420 --> 08:38.020 And one more thing we can call the. 121 08:38.290 --> 08:41.980 We can call the stored procedure using the execute command. 122 08:42.400 --> 08:53.070 But we have to call the function using using Select statement or any DML statements like here we need 123 08:53.070 --> 09:01.110 to give function name and then whatever the parameter are there we need to specify that parameters. 124 09:02.670 --> 09:04.860 So we will see that example also. 125 09:04.860 --> 09:07.320 Now let's see the scalar function. 126 09:23.340 --> 09:24.090 Okay. 127 09:30.810 --> 09:32.490 Uh yes I'm out I'm out. 128 09:32.850 --> 09:33.210 Okay. 129 09:33.210 --> 09:35.340 Now let's see the scalar UDF. 130 09:35.340 --> 09:36.330 Uh example. 131 09:36.330 --> 09:38.160 It returns a scalar value. 132 09:38.940 --> 09:42.420 Now this is a simple syntax. 133 09:42.420 --> 09:46.470 Let's move ahead with encryption. 134 09:46.500 --> 09:47.100 Encryption. 135 09:47.160 --> 09:52.440 What we will do, we cannot see the code written by the developer. 136 09:52.440 --> 09:58.920 And schema binding is we have seen also at the time of you. 137 09:59.250 --> 10:03.210 So let's move ahead by binding the function to the database schema. 138 10:03.240 --> 10:10.230 This prevents you from dropping on or altering the table or view that are used by the function. 139 10:11.550 --> 10:14.400 Okay, now let me create one function. 140 10:15.150 --> 10:24.180 Create a EDF which accepts two scalar integer input, two scalar input integer input, and one character 141 10:24.180 --> 10:31.860 input which specifies the operation to be performed and returns the result of operation. 142 10:32.430 --> 10:38.940 Now over here, we need to create one function in which we need to specify two variables at the rate 143 10:38.970 --> 10:48.530 a and at the rate b, and that will be integer value like it will be ten And it will be 20 now at the 144 10:48.530 --> 10:57.080 Red sea, will be some character value in which we have to specify like plus or minus or multiply. 145 10:57.710 --> 11:04.010 Now we need to store the result into accurate total. 146 11:04.940 --> 11:11.600 Now in the total if we have specify plus operator then total will be returned 30. 147 11:12.650 --> 11:18.200 Now if we specify minus operator then it will be return minus ten right. 148 11:19.670 --> 11:26.450 So based upon whatever the operator we have specified that should be written over here. 149 11:26.480 --> 11:29.180 Now let's see create function. 150 11:29.180 --> 11:30.710 We have given the function name. 151 11:30.710 --> 11:32.090 Function calculate. 152 11:33.020 --> 11:36.980 Let me drop this function if it is already exist. 153 11:40.700 --> 11:42.500 If it is already exist. 154 11:42.530 --> 11:43.280 Yes. 155 11:43.960 --> 11:47.140 Now we have declared two variables at the rate a integer. 156 11:47.170 --> 11:49.630 At the rate b integer and at the rate c character. 157 11:49.630 --> 11:50.260 Right. 158 11:50.950 --> 11:51.850 Now what? 159 11:51.880 --> 11:52.930 What is our requirement? 160 11:52.960 --> 11:54.910 We require returns integer. 161 11:55.300 --> 11:58.450 So this is how function will return. 162 12:02.140 --> 12:04.780 What we have to specify data type over here. 163 12:04.810 --> 12:06.070 Now as begin. 164 12:06.310 --> 12:10.810 Now we have to declare one variable right to store the return result. 165 12:10.840 --> 12:11.440 Right. 166 12:11.860 --> 12:18.940 So for that purpose at the rate total is defined over here that is also integer. 167 12:18.970 --> 12:25.750 Now if address C equal to plus then what we want to do we want to do a plus b. 168 12:26.410 --> 12:31.360 And we want to store the a plus B into some variable at the rate total variable right. 169 12:31.900 --> 12:40.300 Now elseif if else if at the rate c equal to minus, then at the rate a minus b we have to store and 170 12:40.300 --> 12:42.400 else we have to do multiplication. 171 12:42.400 --> 12:47.490 Now let's see how we can execute the function. 172 12:47.940 --> 12:49.440 See over here. 173 12:50.130 --> 12:51.690 Select. 174 12:55.170 --> 12:56.700 Star from. 175 12:56.730 --> 13:03.030 Or we can say we need to specify this function right. 176 13:03.990 --> 13:07.680 We have to specify this function with schema name. 177 13:08.160 --> 13:13.530 Then we need to give two variables at the rate a and at the rate b. 178 13:13.800 --> 13:20.250 And also we need to specify third variable which is plus minus or something. 179 13:20.850 --> 13:24.960 And we need we can give result as alias. 180 13:25.320 --> 13:31.620 Now C cannot find okay I think we need to create this function. 181 13:32.670 --> 13:35.160 Let me create this function. 182 13:35.310 --> 13:36.630 Okay. 183 13:37.530 --> 13:39.780 See it is giving me 30. 184 13:40.380 --> 13:48.560 It is giving me 30 because A is equal to 1020, B equal to ten, and our operator is plus. 185 13:50.270 --> 13:52.610 So over here we have defined plus. 186 13:56.000 --> 13:57.920 So it will give 20 plus ten. 187 13:57.920 --> 14:01.670 Now if I do minus ten see what will happen. 188 14:01.670 --> 14:03.230 It will give me ten. 189 14:04.880 --> 14:06.560 It will give me ten right. 190 14:06.920 --> 14:09.770 Now let me try to do multiply by. 191 14:10.700 --> 14:12.770 So it will give me 200. 192 14:14.720 --> 14:19.280 Now if I specify divide what it will give. 193 14:19.310 --> 14:23.030 Let me try to see else okay. 194 14:23.060 --> 14:23.360 Else. 195 14:23.360 --> 14:30.890 Also it will be multiply by only because over here we have done plus then minus and else. 196 14:31.010 --> 14:36.590 So if plus is not there if minus is not there then it will do multiplication every time. 197 14:38.120 --> 14:43.570 And at the end we need to return total whatever the variable we have declared over here. 198 14:43.780 --> 14:46.360 We need to return that variable. 199 14:46.390 --> 14:47.020 Right. 200 14:49.510 --> 14:55.270 So this is known as a scalar function because it is returning me only a single value. 201 14:55.300 --> 14:55.630 Right. 202 14:55.660 --> 14:58.480 It is returning me only a single value. 203 15:03.520 --> 15:06.130 Now see we can use the function. 204 15:10.510 --> 15:12.520 You can ask the question. 205 15:13.090 --> 15:14.200 Thank you so much. 206 15:14.200 --> 15:16.990 Because I'm not able to ask questions. 207 15:16.990 --> 15:18.550 That is the reason I raise the hands. 208 15:19.270 --> 15:21.610 I think now it will be very, very, very useful. 209 15:21.640 --> 15:21.940 Tips. 210 15:21.940 --> 15:22.540 This is yeah. 211 15:22.540 --> 15:22.690 Yeah. 212 15:22.720 --> 15:25.000 This one is now getting recorded. 213 15:25.330 --> 15:26.740 Can you repeat that same. 214 15:26.740 --> 15:27.850 My question is only one. 215 15:27.880 --> 15:32.770 I think you can say the same question and you can, you know, answer it so that it will be recorded. 216 15:32.770 --> 15:34.000 It will be useful for the entire team. 217 15:34.000 --> 15:34.900 Nobody knows this. 218 15:34.930 --> 15:35.950 Seriously speaking. 219 15:36.220 --> 15:36.700 Yeah. 220 15:36.700 --> 15:39.720 So anyone can use the same function. 221 15:39.900 --> 15:49.290 If you are working in the same organization, then they just have to execute this command again. 222 15:49.320 --> 15:51.480 What is the code written over there? 223 15:51.510 --> 15:52.290 That's my question. 224 15:52.320 --> 15:52.680 Yeah. 225 15:52.680 --> 15:57.540 So we can, uh, see the code using SP underscore help text. 226 15:57.870 --> 16:04.230 And we can copy the code from here and we can paste it into into the new window. 227 16:06.810 --> 16:09.330 We can paste it into the new window. 228 16:13.110 --> 16:13.710 Okay. 229 16:18.000 --> 16:18.810 Makes sense. 230 16:19.110 --> 16:20.040 Okay. 231 16:20.670 --> 16:21.300 Thank you. 232 16:21.330 --> 16:22.050 Yeah. 233 16:23.760 --> 16:29.130 Now we can we can use the function into the select statement as well. 234 16:29.280 --> 16:32.850 So see over here I have used the same function. 235 16:32.970 --> 16:39.680 Now what we want to do we want to we want to add commission into the salary. 236 16:39.950 --> 16:42.860 We want to add commission into the salary. 237 16:42.860 --> 16:44.600 So if I do this one. 238 16:44.810 --> 16:48.290 So see now let me do control D. 239 16:49.580 --> 16:52.610 See now this is 2500 right. 240 16:52.640 --> 16:54.920 2500 or see this one. 241 16:55.010 --> 16:59.450 Now what is the sum of salary and commission that will be total over here right. 242 16:59.480 --> 17:00.950 Salary plus commission. 243 17:00.950 --> 17:02.150 So what I have done. 244 17:02.180 --> 17:05.480 Done over here, this is my salary will become my first parameter. 245 17:05.780 --> 17:09.020 That is usually at the rate a a value right. 246 17:09.050 --> 17:16.820 At the rate a value is become now salary now second value at the rate B value will become my commission. 247 17:17.420 --> 17:23.750 And I have used the commission function because if the commission is null then it will return me zero, 248 17:23.750 --> 17:24.170 right? 249 17:24.170 --> 17:29.870 That is my requirement and I want to add salary plus commission. 250 17:29.870 --> 17:35.900 So that is why I have done over here plus plus and then I have given the alias. 251 17:36.020 --> 17:39.470 So this is the function I have used in the select statement. 252 17:42.260 --> 17:43.610 I can use it in the. 253 17:43.640 --> 17:45.200 Into the Where clause as well. 254 17:45.230 --> 17:51.320 See, I can use the same function into the Where clause as well. 255 17:52.190 --> 17:53.030 Over here. 256 17:53.060 --> 17:54.680 Department equal to 30. 257 17:54.710 --> 17:56.930 I want department equal to 30. 258 17:56.930 --> 17:59.450 I want write like the same way. 259 17:59.480 --> 18:00.260 See below. 260 18:00.260 --> 18:01.850 Query and this query. 261 18:02.840 --> 18:07.070 So both of the query is giving me the same thing right. 262 18:08.570 --> 18:09.920 Give me one minute. 263 18:12.890 --> 18:13.460 Okay. 264 18:13.460 --> 18:15.560 So we can we can see. 265 18:19.310 --> 18:21.560 Can you show me that the select statement one more time. 266 18:23.960 --> 18:25.280 Yeah yeah yeah. 267 18:25.310 --> 18:25.790 Yes yes. 268 18:25.820 --> 18:27.800 This is the select statement. 269 18:31.880 --> 18:39.160 So just the same same way we have to pass the parameters, whether we will use in the From clause or 270 18:39.160 --> 18:41.860 in the Select statement, or in the Where clause. 271 18:42.190 --> 18:42.910 Anywhere. 272 18:42.940 --> 18:46.750 Param function name dbo dot function name, then parameters. 273 18:48.400 --> 18:51.670 Uh one question on the invoking the next one. 274 18:51.670 --> 18:54.850 How come these two select statements is showing department 30? 275 18:54.880 --> 18:59.410 Then for example, if you see that dbo dot function it's a calculator function. 276 18:59.440 --> 19:01.060 It is function calculator. 277 19:01.090 --> 19:01.360 Yeah. 278 19:01.390 --> 19:08.560 Does that uh okay 20 plus ten is 3020 plus and 30 become the input for the department. 279 19:08.590 --> 19:08.920 Yeah. 280 19:08.950 --> 19:09.880 Because of that reason it. 281 19:09.880 --> 19:10.510 Oh God. 282 19:10.510 --> 19:10.780 Okay. 283 19:10.780 --> 19:11.620 Got it I got it. 284 19:11.620 --> 19:12.610 Super beautiful. 285 19:12.640 --> 19:13.210 Beautiful. 286 19:13.240 --> 19:16.330 That is why this both will be the same same output. 287 19:16.720 --> 19:17.860 Got it I got it. 288 19:17.860 --> 19:18.280 Beautiful. 289 19:18.280 --> 19:18.880 Great. 290 19:19.870 --> 19:24.280 So just to see you, we can use the function in the Where clause I have taken. 291 19:24.280 --> 19:32.110 This example function can be used in the Where clause select statement then update your also see over 292 19:32.110 --> 19:33.330 here update statement. 293 19:33.330 --> 19:35.010 Also, I have used the function. 294 19:36.450 --> 19:38.640 This is my simple update statement. 295 19:39.870 --> 19:42.960 Department is equal to what department is equal to 20? 296 19:42.990 --> 19:44.430 I want to do 20. 297 19:45.210 --> 19:49.380 I want to change the some employee department to 20 from 30. 298 19:49.410 --> 19:52.350 Then I can use the function as well right. 299 19:54.180 --> 19:57.060 Then in the in the insert also see in the insert. 300 19:57.060 --> 20:05.130 Also, I have used the function well while giving the input value to the salary column. 301 20:05.640 --> 20:07.800 This is my employee number. 302 20:07.830 --> 20:08.490 First name. 303 20:08.490 --> 20:09.390 Last name. 304 20:10.410 --> 20:11.700 Then I'm not sure. 305 20:11.700 --> 20:12.750 What is this then? 306 20:12.750 --> 20:17.280 This is higher date and this is manager ID this is higher date. 307 20:17.280 --> 20:18.690 This is salary column. 308 20:18.720 --> 20:27.240 See I can input the salary column value 4000 plus 1000 5000 while doing the insert as well. 309 20:27.270 --> 20:27.990 Right. 310 20:28.830 --> 20:33.680 So this is how we can use the function into insert update, delete anywhere, right? 311 20:34.610 --> 20:36.290 We can call the function. 312 20:37.970 --> 20:40.490 Now let's see table valued function. 313 20:41.450 --> 20:43.010 They return table. 314 20:43.850 --> 20:46.460 Now over here inline table valued function. 315 20:46.460 --> 20:50.270 It returns a table based on single select statement. 316 20:51.410 --> 20:53.450 Inline table valued function. 317 20:54.020 --> 20:57.500 It returns a table based on single select statement. 318 20:58.070 --> 21:05.810 In SQL server, we can use these functions to achieve the functionality of parameterized parameterized 319 21:05.810 --> 21:06.590 view. 320 21:06.860 --> 21:10.550 So while defining the view, we cannot give the parameters. 321 21:11.660 --> 21:12.680 But now. 322 21:12.680 --> 21:14.180 So to. 323 21:15.050 --> 21:19.400 To apply that functionality we can use the inline table valued function. 324 21:19.610 --> 21:24.950 Now these are like views but with ability to accept parameters. 325 21:25.760 --> 21:27.380 Inline table valued function. 326 21:27.830 --> 21:32.290 The body of the function will have only a single select statement. 327 21:32.320 --> 21:34.000 Now see the example. 328 21:34.510 --> 21:36.010 See what is this one? 329 21:36.580 --> 21:37.750 Create function. 330 21:37.750 --> 21:39.040 Some function name. 331 21:39.670 --> 21:41.770 Create function function name. 332 21:42.460 --> 21:44.230 Then department integer. 333 21:44.230 --> 21:45.910 We have given the input parameters. 334 21:45.910 --> 21:47.290 Then returns table. 335 21:48.070 --> 21:59.830 Now as return what we are returning, we are returning select star from EMP where department equal to. 336 21:59.860 --> 22:02.170 I think this function will be already exist. 337 22:02.200 --> 22:04.330 Let me drop this function. 338 22:08.530 --> 22:09.610 Drop function. 339 22:09.610 --> 22:10.780 Function name. 340 22:17.920 --> 22:19.540 Let me create this function. 341 22:19.540 --> 22:24.280 Now let's see I can give any department number right. 342 22:24.280 --> 22:27.430 So it will give me all the department records. 343 22:29.520 --> 22:31.950 It will return me all the department letters. 344 22:32.280 --> 22:35.850 Over here I can pass only a single select statement. 345 22:36.840 --> 22:38.580 Not multiple select statement. 346 22:38.610 --> 22:40.200 Only single select statement. 347 22:40.230 --> 22:44.520 That is why it is known as inline table valued function. 348 22:47.730 --> 22:49.710 Inline table valued function. 349 22:49.740 --> 22:52.650 Now let's see another example. 350 22:54.450 --> 23:02.970 Create a function which displays emp id emp name, date of birth department name by passing the gender 351 23:02.970 --> 23:03.630 column. 352 23:03.630 --> 23:05.190 So drop. 353 23:08.850 --> 23:10.200 Function. 354 23:13.770 --> 23:14.640 Function. 355 23:14.640 --> 23:15.270 Name. 356 23:15.300 --> 23:21.930 Now over here we have simply giving the gender because gender should be over in the where clause. 357 23:21.960 --> 23:23.910 Then returns table as written. 358 23:24.030 --> 23:27.710 Now we can return the multiple See. 359 23:27.740 --> 23:29.990 Simple joint joint condition is used. 360 23:29.990 --> 23:30.440 Right? 361 23:30.470 --> 23:32.180 Department and employee. 362 23:33.830 --> 23:38.060 I think department ID some problem is there dept. 363 23:47.240 --> 23:50.180 Let me see the data of this table. 364 23:50.210 --> 23:54.140 Select star from employee. 365 23:57.290 --> 23:58.670 Okay let me use some. 366 23:58.670 --> 24:01.790 Another table EMP one. 367 24:04.700 --> 24:06.050 And dept. 368 24:07.400 --> 24:09.290 Now let me try. 369 24:12.380 --> 24:13.400 Select. 370 24:13.520 --> 24:15.290 But over here dept. 371 24:19.760 --> 24:22.940 Okay now gender column is not there with us right. 372 24:22.970 --> 24:25.190 Gender column is not there with us. 373 24:25.940 --> 24:29.330 So let me use some another column. 374 24:31.160 --> 24:34.610 So in place of gender we can give anything. 375 24:36.230 --> 24:38.780 We can give job a job. 376 24:39.380 --> 24:43.670 Now let me select any of the column MP number. 377 24:46.670 --> 24:47.480 Number. 378 24:47.510 --> 24:51.530 Then Ename Ename will be our second column Ename. 379 24:53.210 --> 24:59.960 Then e dot hire date and department name D name. 380 25:02.330 --> 25:07.100 Then over here to for perform the join dept dept number. 381 25:07.130 --> 25:07.550 Right. 382 25:07.550 --> 25:09.890 So over here dept will be there. 383 25:11.150 --> 25:14.150 And over here dept number will be there. 384 25:15.530 --> 25:21.380 And over here in place of job is equal to a great job. 385 25:22.610 --> 25:24.770 Now let me try to create. 386 25:26.860 --> 25:30.610 Now I am able to create the function. 387 25:32.740 --> 25:36.040 And I have to pass over here what I have to pass over here. 388 25:36.040 --> 25:37.180 I have to pass over here. 389 25:37.210 --> 25:37.660 Job. 390 25:37.660 --> 25:38.260 Right. 391 25:39.280 --> 25:40.810 Ignore the name for now. 392 25:40.810 --> 25:45.760 So see all the detail of the particular job. 393 25:45.790 --> 25:48.940 Whatever we are passing over here that will be written. 394 25:51.040 --> 25:55.420 Over here in the return statement we can use single select statement. 395 25:55.420 --> 25:59.260 But in single select statement we can write any number of join right. 396 25:59.560 --> 26:01.510 We can write any number of join. 397 26:01.510 --> 26:04.120 But select statement will be single one only. 398 26:04.150 --> 26:08.020 So again this one is known as inline table valued function. 399 26:11.770 --> 26:16.060 Now we can pass the default parameter as well. 400 26:16.510 --> 26:21.310 We can pass the default parameter as well while declaring the job. 401 26:21.310 --> 26:24.420 So if we specify over here like, uh. 402 26:28.080 --> 26:28.740 Clark. 403 26:28.740 --> 26:30.720 So this is my default value, right? 404 26:30.870 --> 26:34.110 We can alter the function using alter function. 405 26:34.110 --> 26:35.730 So let me do alter. 406 26:35.760 --> 26:37.860 Now let me alter. 407 26:40.080 --> 26:44.640 Now let me try to execute this function without having parameter. 408 26:49.320 --> 26:51.000 Let me specify default. 409 26:51.000 --> 26:53.850 So it will give me again Clark Clark data. 410 26:54.210 --> 26:59.670 But now if I specify over here something else salesman. 411 27:01.770 --> 27:12.750 If I specify where I will go, if I specify over here salesman then it will give me salesman data. 412 27:12.750 --> 27:13.110 Right. 413 27:13.140 --> 27:16.710 Let me execute both of them. 414 27:16.710 --> 27:17.790 Then you will. 415 27:20.460 --> 27:21.660 Distinguish. 416 27:21.660 --> 27:21.680 Use. 417 27:21.860 --> 27:24.230 So record should be different, right? 418 27:25.910 --> 27:27.380 C record are different. 419 27:27.410 --> 27:29.330 Zero record are of Clark record. 420 27:29.330 --> 27:32.540 And over here are of record of salesman. 421 27:32.540 --> 27:37.310 So if you specify some value then it will ignore the default value. 422 27:37.310 --> 27:46.220 But if you specify the default keyword then it will specify whatever the parameter you have passed at 423 27:46.220 --> 27:48.770 the time of creation that it will take. 424 27:52.700 --> 28:00.290 Now this is again the scalar value example, but this time we have taken two different parameters, 425 28:00.830 --> 28:07.310 two different parameters, and both the parameters we are mentioning in the Where clause. 426 28:10.640 --> 28:18.650 Now let us see Multi-statement table valued function returns a table that is based on multiple statements. 427 28:19.220 --> 28:23.650 So now let us see one few examples of the same. 428 28:25.000 --> 28:27.880 Creating a function which returns a table. 429 28:27.910 --> 28:29.380 Having employee number. 430 28:31.000 --> 28:35.620 So this is again inline table valued function invoking UDF. 431 28:41.980 --> 28:45.220 I think where is the example okay we will okay. 432 28:45.220 --> 28:49.330 Let's see first the difference between the stored procedure and function. 433 28:49.570 --> 28:53.110 Then tomorrow we will see the example of Multi-statement. 434 28:58.330 --> 29:03.700 Multi-statement table valued function I think over here. 435 29:03.700 --> 29:05.440 This is the example of multi-statement. 436 29:05.470 --> 29:06.790 Let us see it quickly. 437 29:07.090 --> 29:15.100 So what we are doing over here see in no, we are not only writing the select statement, we are writing 438 29:15.100 --> 29:15.580 the. 439 29:16.360 --> 29:22.050 We are writing the insert statement as well, using the Select statement. 440 29:22.080 --> 29:29.550 But if we want to specify the insert statement, then we have to use the table variable that we have 441 29:29.550 --> 29:32.100 seen in the theoretical part. 442 29:32.130 --> 29:32.730 Right. 443 29:32.820 --> 29:38.700 So this is my table variable I have declared over here returns at the rate t table. 444 29:39.120 --> 29:40.890 This is my table variable. 445 29:43.800 --> 29:47.850 And I have to specify MP number and MP name. 446 29:47.850 --> 29:48.360 Why. 447 29:48.390 --> 29:54.960 Because in the select statement I am specifying 2 to 2 columns. 448 29:54.960 --> 29:56.490 Right two columns. 449 29:56.520 --> 30:03.420 So whatever the number of columns I have specified in the Select statement that I have to specify over 450 30:03.420 --> 30:07.890 here that columns I have to specify over here. 451 30:07.920 --> 30:08.550 Right. 452 30:08.550 --> 30:11.310 So let me drop this function. 453 30:13.380 --> 30:17.700 Drop function function name if it is exist. 454 30:21.170 --> 30:23.810 Then it will create the function. 455 30:24.080 --> 30:31.490 Now see, it will at one time it will select the data from some particular employee, and it will insert 456 30:31.490 --> 30:33.200 the data into the at the rate t. 457 30:33.530 --> 30:38.630 So now once I will execute this one see what will happen. 458 30:39.500 --> 30:49.010 It will show me all the employee number in employee name which has been written by this particular query. 459 30:50.030 --> 30:53.510 And it is stored in in this particular variable. 460 30:53.510 --> 31:00.920 And if we call this function using this particular department ID then it will show us. 461 31:04.100 --> 31:10.250 So that is why it is known as Multi-statement table valued function. 462 31:13.280 --> 31:19.730 Because with the select statement we have used the insert statement as well at the same time, and to 463 31:19.760 --> 31:24.020 use insert, update or delete, we need to use the stable variable. 464 31:25.880 --> 31:28.790 That is why stable variable is used over here, right? 465 31:32.900 --> 31:35.030 That is why stable variable is used over here. 466 31:35.060 --> 31:37.820 Now one very important. 467 31:39.950 --> 31:41.630 Important function. 468 31:41.660 --> 31:44.030 Important difference over here. 469 31:44.240 --> 31:47.120 Store procedure and store function. 470 31:47.720 --> 31:51.170 And that they are asking many a times. 471 31:52.010 --> 31:58.700 Now let me try to remove this one and let me show you whether it will work or not. 472 31:59.840 --> 32:00.290 Okay. 473 32:00.320 --> 32:00.800 Over here. 474 32:00.800 --> 32:01.970 It is working. 475 32:04.670 --> 32:07.730 Let me try somewhere else. 476 32:13.280 --> 32:13.760 Okay. 477 32:13.760 --> 32:15.910 Over here also it is working. 478 32:21.040 --> 32:24.190 I think because of by default schema is dbo schema. 479 32:24.220 --> 32:25.960 That is why it is working. 480 32:26.110 --> 32:28.600 By default schema is a dbo schema. 481 32:31.510 --> 32:32.950 So that is a simple thing. 482 32:33.100 --> 32:36.220 Let's start the difference between stored procedure and function. 483 32:36.760 --> 32:39.370 So now say function must return. 484 32:39.370 --> 32:44.710 Function must return a single value which may be a scalar or a table. 485 32:46.000 --> 32:50.020 So we have seen the scalar function as well as table valued function. 486 32:50.020 --> 32:51.850 Both the function we have seen right. 487 32:51.940 --> 32:56.650 Whereas stored procedure can return zero, single or multiple values. 488 32:57.190 --> 33:05.260 Stored procedure can return zero value or single value or multiple value that yesterday we have seen. 489 33:05.260 --> 33:05.770 Right. 490 33:05.800 --> 33:07.540 We have used the return. 491 33:07.750 --> 33:14.490 If we want multiple value with the stored procedure that then we can have to use output parameter. 492 33:14.520 --> 33:14.790 Right. 493 33:14.820 --> 33:16.230 Output parameter. 494 33:17.970 --> 33:21.300 But output parameter cannot be used in the function. 495 33:23.040 --> 33:23.910 Right? 496 33:24.030 --> 33:32.820 Ten for single single value, we can write the return statement in the store procedure as well that 497 33:32.820 --> 33:34.590 also we have seen the yesterday. 498 33:34.950 --> 33:42.900 Now let's see for executing or calling the function, the function should be a part of a Select statement. 499 33:43.560 --> 33:45.930 The function should be a part of a Select statement. 500 33:45.930 --> 33:46.560 Right? 501 33:47.760 --> 33:52.470 But we we can speak can be executed using execute or execute. 502 33:52.950 --> 33:53.850 Uh keyword. 503 33:53.880 --> 34:02.730 Right now function can have only input parameters whereas store procedure can have both input and output 504 34:02.730 --> 34:03.690 parameters. 505 34:04.080 --> 34:07.770 We cannot use the output over here right in this function. 506 34:10.860 --> 34:14.840 Now function cannot make the changes in the DB object. 507 34:15.950 --> 34:18.710 ESP can make the changes in the db object. 508 34:18.740 --> 34:24.170 Function cannot issue insert, update delete statement against table or use in db. 509 34:25.340 --> 34:26.270 But we can. 510 34:26.300 --> 34:28.460 We can do that. 511 34:28.490 --> 34:32.210 Insert, update and delete in the function using a table variable. 512 34:32.870 --> 34:35.240 So for that purpose table variable must. 513 34:35.270 --> 34:38.930 We have to use directly in a table or you. 514 34:38.960 --> 34:42.650 We cannot write, insert, update and delete right. 515 34:48.920 --> 34:54.080 And ESP can issue, insert, update and delete statement against the db object. 516 34:54.770 --> 34:57.620 Now let's see forward. 517 35:00.440 --> 35:03.890 Function does not support any Trycatch block. 518 35:03.920 --> 35:10.460 We have seen the try catch block in the stored procedure, but we cannot use the try catch block over 519 35:10.460 --> 35:12.010 here right in the function. 520 35:12.040 --> 35:13.660 We cannot use it. 521 35:14.650 --> 35:24.070 Now transaction are supported in the stored procedure but in the function does not support support transaction 522 35:24.070 --> 35:24.550 management. 523 35:24.550 --> 35:25.120 Right. 524 35:28.120 --> 35:34.690 Now procedure cannot be called from the UDF or procedure cannot be called from the okay. 525 35:34.690 --> 35:42.040 We cannot call the procedure inside the stored function, but function can be called from a procedure. 526 35:42.880 --> 35:45.940 Function can be called from the procedure. 527 35:47.170 --> 35:50.350 And this is one more important difference. 528 35:50.350 --> 35:53.890 Function does not support deferred name resolution. 529 35:54.670 --> 35:58.540 Function does not support the default name resolution. 530 35:58.540 --> 36:02.440 Now let me try to explain this one. 531 36:02.470 --> 36:05.530 Select star. 532 36:07.480 --> 36:08.920 Select star. 533 36:11.520 --> 36:17.400 Into AMP 17 zero nine from AMP one. 534 36:18.030 --> 36:24.540 Now let's see I have created this table right I have created this table. 535 36:24.750 --> 36:27.600 Now let me create the store procedure. 536 36:27.600 --> 36:28.500 Create. 537 36:31.800 --> 36:32.820 Procedure. 538 36:32.970 --> 36:34.350 Step one. 539 36:35.490 --> 36:41.430 Then select star from this particular table name. 540 36:45.060 --> 36:48.150 Now let me try to. 541 37:01.740 --> 37:04.620 I forget the syntax. 542 37:05.130 --> 37:05.550 Okay. 543 37:05.550 --> 37:06.660 It is created. 544 37:06.780 --> 37:11.060 Let me now try to execute this 1SP1. 545 37:13.340 --> 37:16.640 So so I see it will return me all the data from this table. 546 37:16.670 --> 37:17.240 Right. 547 37:17.630 --> 37:18.680 Now see. 548 37:18.710 --> 37:20.930 Now see what I am doing. 549 37:23.300 --> 37:26.240 I am trying to drop this procedure again. 550 37:26.240 --> 37:27.650 Drop procedure. 551 37:28.550 --> 37:29.660 This SP1. 552 37:29.660 --> 37:32.240 Or let me try to create new procedure. 553 37:32.300 --> 37:39.650 And I am dropping this this this table I am dropping this table drop table this table name. 554 37:40.070 --> 37:46.580 See I am dropping this table now I am trying to create this procedure. 555 37:47.750 --> 37:48.740 See over here. 556 37:48.740 --> 37:51.800 This procedure is still executed. 557 37:52.670 --> 37:54.560 This procedure is still created. 558 37:54.560 --> 37:59.870 But now when I am trying to execute this procedure, see what it will give me. 559 38:01.040 --> 38:02.960 Invalid object name. 560 38:02.960 --> 38:04.850 Invalid object name. 561 38:06.320 --> 38:11.480 While at the time of executing the store procedure. 562 38:11.660 --> 38:14.630 But now I am trying to create. 563 38:18.470 --> 38:22.430 The function I am trying to create the function now. 564 38:23.750 --> 38:25.520 Now see what will happen. 565 38:25.550 --> 38:28.490 Let me create some another function name. 566 38:29.300 --> 38:33.890 Now let's see what will happen in the procedure. 567 38:33.890 --> 38:35.150 It will be able to. 568 38:35.180 --> 38:39.620 It will not giving any error at the time of creation. 569 38:41.330 --> 38:44.390 Okay I think this table is there or not. 570 38:44.750 --> 38:46.400 Let me see. 571 38:54.110 --> 38:57.110 Actually, this should return me some error. 572 38:58.340 --> 39:01.640 I don't know why it is not giving me any error. 573 39:02.360 --> 39:04.100 It should return one minute. 574 39:29.620 --> 39:31.600 See now, now. 575 39:31.990 --> 39:32.350 Okay. 576 39:32.380 --> 39:32.920 Over here. 577 39:32.920 --> 39:34.240 At the time of. 578 39:37.270 --> 39:39.760 Now, let me try to alter this one. 579 39:45.190 --> 39:48.670 See, while altering the store procedure. 580 39:48.670 --> 39:50.350 This is giving me error. 581 39:50.380 --> 39:50.980 Sorry. 582 39:50.980 --> 39:52.030 Altering the function. 583 39:52.030 --> 39:54.400 It is giving me the error if I. 584 39:54.400 --> 39:55.600 If I drop this. 585 39:55.750 --> 39:57.370 If I drop this function. 586 39:57.400 --> 39:59.020 Drop function. 587 40:04.200 --> 40:05.820 Function name. 588 40:05.850 --> 40:13.170 And now, if I try to create this, uh, if I try to create this function, this emp1 table is not exist. 589 40:13.200 --> 40:13.470 Right. 590 40:13.470 --> 40:17.070 If I try to create this function, it will give me error. 591 40:17.100 --> 40:19.200 Invalid object name Emp1. 592 40:19.230 --> 40:23.520 But at the time of stored procedure it is not giving me any error, right? 593 40:23.520 --> 40:31.440 So this is the last difference it is telling function does not support support deferred name resolution. 594 40:31.860 --> 40:34.470 ESP supports deferred name resolution. 595 40:35.940 --> 40:36.600 ESP support. 596 40:36.600 --> 40:38.490 Deferred deferred name resolution. 597 40:38.520 --> 40:44.970 It means even though the table is not exist, we can create the stored procedure at the time of execution. 598 40:44.970 --> 40:51.060 It will give me error, but while the table is not exist, we cannot create the function. 599 40:51.060 --> 40:54.720 It will throw some error like invalid object name. 600 40:55.410 --> 40:59.640 Right now you can ask me if you have any question. 601 41:00.630 --> 41:02.760 Otherwise we will meet again tomorrow.