1 00:00.650 --> 00:04.490 So today we are going to see the dynamic SQL. 2 00:05.450 --> 00:10.010 And this is actually very rarely used concept. 3 00:10.490 --> 00:19.430 We can say in some of the companies they are using it in the development for development, but still 4 00:19.430 --> 00:20.750 it is very rarely used. 5 00:20.780 --> 00:22.400 Now what is dynamic SQL? 6 00:22.400 --> 00:28.430 So SQL command commands at runtime are called dynamic SQL commands. 7 00:30.230 --> 00:35.870 Now dynamic SQL is useful when we do not know table names and column names until runtime. 8 00:36.950 --> 00:39.770 So now let's see one of the example. 9 00:39.800 --> 00:40.430 See. 10 00:41.060 --> 00:43.430 Now this is one example. 11 00:44.000 --> 00:50.090 We have taken one variable t t table name, t name and stmt. 12 00:51.020 --> 00:56.120 And now we have defined this t name over here which is a table name. 13 00:56.420 --> 01:04.580 So over here emp table name I have taken and we have said that AMP value into this table name variable. 14 01:04.610 --> 01:04.820 Right. 15 01:05.030 --> 01:06.050 Name variable. 16 01:06.350 --> 01:10.310 Now Stmt is one more variable is there. 17 01:11.090 --> 01:16.850 And in that particular variable we are select star from. 18 01:16.850 --> 01:20.270 So see this is not a normal Select statement right. 19 01:20.300 --> 01:23.930 This select statement is written in single quote. 20 01:23.930 --> 01:27.080 So over here it will be fixed up to. 21 01:27.110 --> 01:35.180 Here it will be fixed select star from now table name I have taken from the variable table name T name 22 01:35.180 --> 01:37.580 I have taken from the variable which is emp. 23 01:38.060 --> 01:40.130 And now I can execute. 24 01:40.220 --> 01:45.680 I can execute this uh dynamic SQL using execute command. 25 01:45.680 --> 01:47.360 So we can see x EQ. 26 01:48.140 --> 01:50.930 And we have to execute this statement right. 27 01:50.930 --> 01:54.170 So it will ultimately it will give me this one only. 28 01:55.400 --> 01:56.630 I am just. 29 02:00.170 --> 02:02.150 Ultimately it will give me this. 30 02:02.150 --> 02:04.430 This select star from table name only. 31 02:04.880 --> 02:09.530 This is another way we can write a dynamic SQL. 32 02:09.740 --> 02:17.420 So it will give me 16 record and this one will also give me 16 record. 33 02:17.480 --> 02:21.170 But now over here you can change this table name right. 34 02:21.200 --> 02:26.870 If you change this to dept then it will run for dept table. 35 02:28.490 --> 02:31.310 You do not need to write another select statement. 36 02:31.310 --> 02:36.140 You just need to mention over here different variable name. 37 02:39.860 --> 02:44.030 So where variable name will be same but different value to this variable name. 38 02:44.060 --> 02:46.880 So this is one example of dynamic SQL. 39 02:48.560 --> 02:56.240 Now dynamic SQL can be executed by using execute or execute command or SP execute procedure. 40 02:57.020 --> 02:59.600 And which suppose input and output parameters. 41 03:00.770 --> 03:02.330 Now let's see. 42 03:03.320 --> 03:07.190 Let me see whether this table is exist or not. 43 03:07.220 --> 03:07.700 Okay. 44 03:07.700 --> 03:09.230 It is not exist. 45 03:09.980 --> 03:11.870 So let me create this table. 46 03:11.900 --> 03:17.690 Now see create a procedure to drop a particular particular table. 47 03:19.100 --> 03:23.420 So if you see now this table is available. 48 03:23.420 --> 03:25.880 And above table is also available right. 49 03:26.420 --> 03:28.820 EMP 11 table is now available. 50 03:28.880 --> 03:38.090 But I want to drop this table using some store procedure dynamic store procedure or dynamic SQL. 51 03:38.120 --> 03:42.680 So what I can do create procedure procedure name. 52 03:43.250 --> 03:47.690 Then I have taken this t name as where care of 20. 53 03:47.720 --> 03:47.990 Right. 54 03:48.020 --> 03:49.370 Where care of 20. 55 03:49.550 --> 03:53.120 And I have to pass this t name at runtime. 56 03:53.840 --> 03:56.540 I have to pass this t name at runtime. 57 03:56.540 --> 03:58.310 So as execute. 58 03:58.580 --> 04:00.230 Now what is written over here? 59 04:00.260 --> 04:01.640 See drop table. 60 04:01.910 --> 04:03.170 Table name. 61 04:03.770 --> 04:04.550 Drop table. 62 04:04.580 --> 04:05.030 Table name. 63 04:05.030 --> 04:05.810 This will be fixed. 64 04:05.840 --> 04:06.440 Right. 65 04:06.890 --> 04:08.240 And this is a. 66 04:09.230 --> 04:12.950 Is there a table name will be concat with this variable. 67 04:14.930 --> 04:15.950 This drop table. 68 04:15.980 --> 04:16.280 Right. 69 04:16.280 --> 04:26.090 So I need to while executing I need to mention execute then drop table procedure name and then which 70 04:26.090 --> 04:31.250 table I want to drop that table name I have to specify right. 71 04:31.250 --> 04:35.750 So now if I execute this query let's see. 72 04:38.090 --> 04:39.260 Could not find. 73 04:39.290 --> 04:39.890 Okay. 74 04:39.890 --> 04:42.650 Let me create this procedure. 75 04:45.110 --> 04:49.970 If I execute this procedure now this table will not be available. 76 04:50.000 --> 04:52.670 See invalid object name. 77 04:52.670 --> 04:54.890 This table is now not available. 78 04:56.780 --> 04:59.900 So once again, this is a runtime. 79 04:59.900 --> 05:05.090 We are deciding which table we want to drop, which table we want to drop. 80 05:05.120 --> 05:06.920 We are designing runtime. 81 05:08.900 --> 05:12.350 You can mention over here in the parameter any table name. 82 05:13.070 --> 05:16.130 So it will drop drop that table name right. 83 05:19.820 --> 05:21.020 Now. 84 05:23.300 --> 05:25.400 Let's see one more example. 85 05:25.400 --> 05:29.270 Let me create this procedure. 86 05:31.310 --> 05:31.520 Okay. 87 05:31.520 --> 05:33.170 It is already available. 88 05:33.260 --> 05:40.400 Now execute query I have created this procedure and in which declare t name declare t name. 89 05:40.400 --> 05:42.200 So one variable is defined. 90 05:42.410 --> 05:45.500 What is the table name I want to display over here. 91 05:45.500 --> 05:50.210 So EMP one it is static table name right now column name. 92 05:50.210 --> 05:53.060 What are the columns I want to retrieve from this table. 93 05:53.060 --> 05:55.640 So E name and job. 94 05:57.350 --> 06:04.340 ahead and declare column and declare column two. 95 06:04.370 --> 06:06.260 And now I want to store. 96 06:06.290 --> 06:07.940 I want to create one query. 97 06:07.970 --> 06:13.730 Dynamic query in which column name and table name will be. 98 06:16.100 --> 06:17.240 Peaking at runtime. 99 06:17.240 --> 06:17.540 Right. 100 06:17.780 --> 06:20.900 Column name and table name will be peaking at runtime. 101 06:20.990 --> 06:25.550 So in SMTP variable I am storing this entire query. 102 06:25.580 --> 06:26.600 Query. 103 06:27.110 --> 06:28.640 What is a column name? 104 06:29.990 --> 06:31.880 So select column one. 105 06:31.880 --> 06:34.850 So column one will be our name. 106 06:34.880 --> 06:37.880 Column two will be our job. 107 06:37.970 --> 06:41.870 And from table name will be EMP. 108 06:41.900 --> 06:46.520 I think I need to alter this procedure. 109 06:46.550 --> 06:47.420 Alter. 110 06:53.690 --> 06:54.470 Alter this. 111 06:55.070 --> 06:56.770 I have altered this procedure. 112 07:00.520 --> 07:04.840 Now, if I again alter, let me alter this again. 113 07:06.010 --> 07:06.850 Okay. 114 07:06.880 --> 07:09.370 Now let me execute this procedure. 115 07:09.520 --> 07:14.890 SP execute x e query exp execute x query. 116 07:14.890 --> 07:16.570 So see what it will return. 117 07:18.520 --> 07:19.690 It is. 118 07:22.030 --> 07:22.960 It is. 119 07:23.140 --> 07:24.610 It is deriving one query. 120 07:24.640 --> 07:30.070 See it is deriving one query or dynamically at runtime. 121 07:30.550 --> 07:32.290 So this is the particular query. 122 07:32.290 --> 07:33.430 It is derived. 123 07:35.710 --> 07:37.210 From this stored procedure. 124 07:37.420 --> 07:40.960 It has taken this column one column two. 125 07:40.960 --> 07:42.670 And from this table name. 126 08:00.760 --> 08:01.750 Question. 127 08:02.170 --> 08:03.010 Uh, yes. 128 08:03.760 --> 08:10.330 Uh, was that was that, uh, um, no data in the EMP? 129 08:10.360 --> 08:11.170 EMP then? 130 08:11.950 --> 08:21.100 No, no, uh, EMP table has a data, but this statement will, uh, will create a query only. 131 08:21.130 --> 08:28.420 We have to copy paste the query from here into our SSMs. 132 08:28.810 --> 08:29.380 Okay, fine. 133 08:29.410 --> 08:34.300 And then we then if we execute this query, then it will give me data. 134 08:34.510 --> 08:35.080 Got it. 135 08:35.080 --> 08:35.440 Got it. 136 08:35.440 --> 08:37.750 So previously we write a query. 137 08:37.780 --> 08:42.400 Now we are, uh creating a query itself to create a query. 138 08:43.570 --> 08:44.470 Uh, beautiful. 139 08:44.590 --> 08:45.400 Yes yes, yes. 140 08:45.910 --> 08:46.840 Now, one more thing. 141 08:46.840 --> 08:47.740 One more thing. 142 08:47.860 --> 08:53.920 Uh, on this, uh, um, uh, I'm not sure whether you have covered it or not, but still, I'm asking 143 08:53.920 --> 08:54.190 it. 144 08:54.220 --> 08:55.780 Say for example, if it is going to be. 145 08:55.810 --> 09:03.520 VB during the runtime, we used to declare a variable and get the input from the user and then displaying 146 09:03.520 --> 09:04.390 based on it. 147 09:04.600 --> 09:08.170 Do we have that functionality or did I miss anything here? 148 09:08.710 --> 09:14.290 No, actually that functionality is available in the store procedure at the time of store procedure. 149 09:14.530 --> 09:22.780 What we are doing, we are creating the variables only and at the time of runtime like execute ESP name. 150 09:22.930 --> 09:29.980 And we need to give that uh, that variables like uh for emp id ten. 151 09:30.010 --> 09:36.010 And what are the name uh like we have to declare the variable. 152 09:36.010 --> 09:41.860 We have seen this at the time of store procedure, but still in the store procedure. 153 09:41.860 --> 09:45.520 Also, we are writing the name and then we are running it. 154 09:45.550 --> 09:55.120 My question is dynamically during the runtime, uh, you know, SQL has to ask for the, uh, you know, 155 09:55.150 --> 09:57.130 uh, which one has to be displayed. 156 09:57.160 --> 10:00.910 Let's say, for example, if it is the employee name, which I am very, very sure about, our employee 157 10:00.910 --> 10:02.260 number, which I am very sure about it. 158 10:02.260 --> 10:05.680 So I should put an employee number by that time. 159 10:05.710 --> 10:08.380 And I ask to display based on it. 160 10:11.410 --> 10:11.830 Yeah. 161 10:11.830 --> 10:16.870 But it will not provide that facility to provide the value at runtime. 162 10:16.870 --> 10:24.910 We need to give it menu manually only because this is not a this is not a front. 163 10:24.940 --> 10:25.360 Yeah. 164 10:25.390 --> 10:31.780 But it means at the time of running store procedure or at the time of inside a query, anything will 165 10:31.810 --> 10:32.650 be fine. 166 10:32.890 --> 10:37.510 Like it will not open a open a pop up box and it will ask us to enter. 167 10:38.650 --> 10:39.070 Beautiful. 168 10:39.070 --> 10:41.560 So yeah the functionality right. 169 10:41.830 --> 10:44.950 That is a functionality of front end application. 170 10:45.040 --> 10:46.630 Front end application okay okay. 171 10:46.660 --> 10:48.130 VB is a front end application. 172 10:48.160 --> 10:50.200 This is a back end right? 173 10:50.920 --> 10:52.180 Okay, fine fine fine. 174 10:52.180 --> 10:56.020 So so you are very sure that that kind of functionality is not there, right? 175 10:56.350 --> 10:56.830 Oh, yes. 176 10:56.830 --> 10:57.400 Yes. 177 10:58.090 --> 10:58.690 Okay, fine. 178 10:58.690 --> 10:59.110 Thanks. 179 10:59.110 --> 10:59.980 That's my question. 180 11:00.550 --> 11:01.480 Okay, okay. 181 11:04.720 --> 11:06.760 Now here the query is defined. 182 11:06.790 --> 11:09.880 Now let me see one more thing. 183 11:10.570 --> 11:11.260 Mhm. 184 11:12.760 --> 11:13.390 Implement. 185 11:13.420 --> 11:14.200 Okay. 186 11:14.230 --> 11:19.060 So now see uh there is one, one change over here. 187 11:19.090 --> 11:19.750 Over here. 188 11:19.750 --> 11:25.180 We haven't passed any static value like we have passed over here. 189 11:25.180 --> 11:25.690 Table name. 190 11:25.690 --> 11:26.260 Right. 191 11:26.440 --> 11:27.820 We have passed over here. 192 11:27.820 --> 11:28.510 Table name. 193 11:28.510 --> 11:29.290 Column name. 194 11:29.290 --> 11:30.310 Column name. 195 11:30.580 --> 11:33.820 But in this store procedure we haven't passed anything. 196 11:33.820 --> 11:37.660 Let me try to execute it. 197 11:37.660 --> 11:37.990 Okay. 198 11:38.020 --> 11:40.120 It is executed now. 199 11:40.120 --> 11:42.070 It will ask us runtime. 200 11:42.430 --> 11:45.430 It will ask us runtime which table name. 201 11:45.430 --> 11:48.790 So select column one column name from table name. 202 11:48.790 --> 11:55.540 So which table name I want to check it for MP and we need to pass two column names. 203 11:55.540 --> 12:01.690 So over here it is giving me data over here while executing the query. 204 12:01.690 --> 12:03.550 It is giving me data as well. 205 12:03.580 --> 12:04.780 See over here. 206 12:09.640 --> 12:12.310 So so runtime I can change this value. 207 12:12.310 --> 12:19.360 If I know the department dept table I can select and dept name. 208 12:19.420 --> 12:24.850 Uh one minute select star from. 209 12:27.760 --> 12:29.710 Department which are the columns? 210 12:29.740 --> 12:33.250 Are there id and dept name. 211 12:33.550 --> 12:39.430 So I can just I need to uh change the variable over here. 212 12:39.910 --> 12:41.740 Change the parameter over here. 213 12:41.740 --> 12:44.860 It, it will be id and. 214 12:47.080 --> 12:48.430 Dept name. 215 12:51.430 --> 12:56.380 Now if I execute this query, it will give me that state information. 216 12:57.100 --> 13:07.600 Now if I if I have ten columns in a table right then I need to I need to copy paste this 110 times right. 217 13:08.080 --> 13:11.380 I need to copy paste this 110 times. 218 13:13.630 --> 13:17.500 Something like something like this over here. 219 13:17.500 --> 13:21.160 Two plus are there, but maybe only one plus will be there. 220 13:21.550 --> 13:29.320 So if a if I, I can I can retrieve now ten I need to give over here ten columns. 221 13:30.880 --> 13:34.270 So it will give me all the ten columns of any table. 222 13:37.450 --> 13:38.920 Whichever is ten column. 223 13:38.950 --> 13:39.130 Right. 224 13:39.160 --> 13:41.380 So over here everything is dynamic. 225 13:43.120 --> 13:44.320 There is no select. 226 13:44.320 --> 13:47.560 We have just selecting column name and table name. 227 13:48.550 --> 13:54.970 But this thing you have to remember, you have to pass select in the single code you have to pass select 228 13:54.970 --> 14:02.350 in the single code, and from also in the single code you have to derive query like that. 229 14:03.340 --> 14:10.870 Otherwise if if this query is wrong then it will not give us the or it will fail while runtime. 230 14:15.280 --> 14:17.230 It will fail while runtime. 231 14:26.020 --> 14:28.390 Let me try to add one more column. 232 14:28.510 --> 14:30.220 Let me try. 233 14:31.780 --> 14:32.770 So over here. 234 14:32.800 --> 14:39.100 Plus then this, then this and this. 235 14:39.100 --> 14:41.140 Column three. 236 14:41.830 --> 14:43.660 Let me try to alter. 237 14:43.690 --> 14:46.990 I am not sure whether it is correct. 238 14:49.540 --> 14:49.870 Okay. 239 14:49.870 --> 14:50.980 What is wrong? 240 14:56.530 --> 14:57.040 Over here. 241 14:57.070 --> 15:00.640 Plus it is starting column name plus again. 242 15:01.960 --> 15:02.230 Then. 243 15:02.260 --> 15:03.280 Plus. 244 15:12.820 --> 15:13.690 Can you identify? 245 15:13.720 --> 15:16.390 Can anyone identify what is wrong over here? 246 15:23.290 --> 15:26.110 To to derive this kind of query is very. 247 15:27.700 --> 15:30.280 Sometimes it is difficult. 248 15:32.170 --> 15:32.350 Okay. 249 15:32.350 --> 15:37.030 Let me copy this one or up to this. 250 15:39.160 --> 15:40.870 Let me write it over here. 251 15:40.870 --> 15:42.670 And now let's see. 252 15:48.280 --> 15:49.480 Okay, this is wrong. 253 15:49.480 --> 15:51.850 I need to define this column three also over here. 254 15:51.850 --> 15:52.390 Right. 255 15:54.040 --> 15:56.230 Column three also here. 256 15:56.260 --> 16:00.880 Now if I run order two column two, you need a no no. 257 16:04.000 --> 16:08.230 That is you need comma where comma. 258 16:08.260 --> 16:08.470 Comma. 259 16:08.470 --> 16:09.280 Column two. 260 16:10.270 --> 16:10.630 No, no. 261 16:11.170 --> 16:13.180 Top of it okay. 262 16:13.180 --> 16:14.530 Yes yes yes yes yes. 263 16:16.120 --> 16:18.760 Now let me try to execute this one. 264 16:18.760 --> 16:19.930 It will execute. 265 16:19.930 --> 16:28.090 And I can again write EMP and I forget what are the columns EMP. 266 16:32.800 --> 16:34.600 MP number name and job. 267 16:34.600 --> 16:35.980 So EMP. 268 16:39.010 --> 16:40.330 Number. 269 16:43.840 --> 16:44.740 E name. 270 16:45.010 --> 16:47.080 And I can specify. 271 16:47.960 --> 16:48.560 job. 272 16:48.560 --> 16:50.570 So now let me try to execute. 273 16:51.440 --> 16:54.650 See it is giving me three columns right now. 274 16:54.680 --> 16:58.760 Now if I pass two whether it will be successful or not. 275 16:58.760 --> 16:59.540 Let's see. 276 17:00.050 --> 17:05.030 No procedure or function expects parameter column three which was not supplied. 277 17:05.510 --> 17:08.930 So we are supplying this at runtime now right. 278 17:12.890 --> 17:15.170 We are supplying this at runtime. 279 17:16.370 --> 17:22.880 Now let's see what this create a procedure to drop all the tables from the database. 280 17:24.260 --> 17:28.160 Create a procedure to drop all the tables from the database. 281 17:28.190 --> 17:33.290 Now let me experiment this one in some other database. 282 17:33.290 --> 17:37.310 So let me try to experiment in. 283 17:38.060 --> 17:41.630 So now there will be some times they are asking us right. 284 17:41.660 --> 17:46.070 Like like we need to drop all the tables from the database. 285 17:46.070 --> 17:48.760 We need to drop all the tables from the database. 286 17:48.910 --> 17:52.000 So at that time you are not doing it manually, right? 287 17:52.000 --> 17:53.260 You cannot do it. 288 17:54.610 --> 18:01.720 Uh like go to this database and right click and then drop. 289 18:02.200 --> 18:04.090 That is not a right choice right. 290 18:04.090 --> 18:05.770 So let me try. 291 18:06.730 --> 18:08.140 So what it will happen. 292 18:08.140 --> 18:13.300 So see create procedure drop all tables as now over here we are. 293 18:13.330 --> 18:18.160 We have to loop through all the tables so that I have declared a cursor over here. 294 18:18.160 --> 18:22.120 Declare see one cursor for select table name. 295 18:22.120 --> 18:27.250 Select table name for information schema dot tables where table type is equal to base table. 296 18:27.820 --> 18:29.530 This one we have seen yesterday. 297 18:29.530 --> 18:30.760 Right base table. 298 18:30.910 --> 18:36.520 Now as the table name and SMTP now open we have to open the cursor right. 299 18:36.550 --> 18:42.160 We have to open the cursor and fetch next from C1 into this table name. 300 18:42.490 --> 18:45.550 We want table name one by one row right. 301 18:45.610 --> 18:48.490 first table, then second table, then third table. 302 18:48.490 --> 18:49.330 Like this. 303 18:50.170 --> 18:52.510 While face status is equal to zero. 304 18:52.540 --> 18:53.590 Begin. 305 18:54.160 --> 18:57.670 Now in this particular variable I need to store the query. 306 18:57.700 --> 18:58.120 Right. 307 18:58.150 --> 18:59.050 Drop table. 308 18:59.080 --> 19:00.280 Table name one. 309 19:00.490 --> 19:01.270 Drop table. 310 19:01.300 --> 19:01.600 Table. 311 19:01.630 --> 19:02.260 Name two. 312 19:02.290 --> 19:03.550 In second iteration. 313 19:03.580 --> 19:04.270 Drop table. 314 19:04.300 --> 19:05.380 Table name three. 315 19:05.830 --> 19:07.750 Now I have to execute this one. 316 19:07.750 --> 19:13.540 So execute this statement and fetch next from C1 into table name. 317 19:13.540 --> 19:16.390 So again I want to fetch next record every time. 318 19:17.410 --> 19:18.820 Next record every time. 319 19:18.820 --> 19:21.730 So closing the cursor and deallocating the cursor. 320 19:22.810 --> 19:25.900 Now let me execute this query. 321 19:25.900 --> 19:29.530 And we have all the tables are available in our database. 322 19:30.490 --> 19:33.010 SQL underscore projects database right. 323 19:33.790 --> 19:37.360 Now let me try drop procedure. 324 19:43.480 --> 19:44.410 Sorry. 325 19:44.410 --> 19:47.560 Execute procedure. 326 19:49.630 --> 19:51.970 Or execute this statement. 327 19:52.000 --> 19:53.200 Now let me see. 328 19:56.260 --> 19:59.020 Cannot drop cells because it does not exist. 329 19:59.020 --> 20:00.670 Or you do not have permission. 330 20:00.700 --> 20:02.020 Cannot drop. 331 20:02.050 --> 20:02.890 Why? 332 20:06.610 --> 20:06.940 Okay. 333 20:06.970 --> 20:08.260 Foreign key. 334 20:08.290 --> 20:09.820 This is constraint related. 335 20:09.820 --> 20:11.200 Problems are there. 336 20:12.160 --> 20:12.730 Over here. 337 20:12.730 --> 20:13.510 Foreign key. 338 20:13.540 --> 20:14.170 Foreign key. 339 20:14.170 --> 20:15.280 Constraints are there. 340 20:15.280 --> 20:16.090 So. 341 20:18.880 --> 20:20.770 Or we can do one thing. 342 20:21.370 --> 20:23.080 Let me do it quickly. 343 20:23.080 --> 20:24.160 Let me do. 344 20:28.660 --> 20:31.990 Create database test. 345 20:33.340 --> 20:35.230 Let me create a test database. 346 20:35.260 --> 20:35.590 Okay. 347 20:35.590 --> 20:36.760 It is created. 348 20:41.200 --> 20:43.510 Now let me refresh. 349 20:44.370 --> 20:46.860 I will go to this test database. 350 20:48.810 --> 20:50.970 I will go over here. 351 20:53.430 --> 20:55.470 Whether it has any table or not. 352 20:55.500 --> 20:56.190 So okay. 353 20:56.190 --> 20:57.600 It has no table. 354 20:57.990 --> 20:59.640 Now let me create a table. 355 20:59.640 --> 21:08.070 Create table emp id integer and name where care of ten. 356 21:11.010 --> 21:17.790 I have created this one table and let me create one more table dpt. 357 21:18.030 --> 21:19.560 ID integer. 358 21:20.070 --> 21:22.890 Let it keep only one one column. 359 21:22.890 --> 21:25.110 Let me create both of these table. 360 21:25.140 --> 21:25.560 Okay. 361 21:25.590 --> 21:26.970 One more table. 362 21:28.800 --> 21:35.880 Let me insert the data insert into EMP values one comma Harshit. 363 21:39.000 --> 21:41.970 Let me insert one more value. 364 21:44.340 --> 21:45.180 Raj. 365 21:45.330 --> 21:45.780 Okay. 366 21:45.810 --> 21:48.360 Two values are inserted and now. 367 21:50.550 --> 21:52.080 Insert into DPT. 368 21:53.010 --> 21:54.750 Values one. 369 21:55.650 --> 21:59.040 We can see multiple values also at one time, right? 370 21:59.040 --> 22:01.800 We can insert multiple values as one time. 371 22:01.890 --> 22:05.610 So this is how you can insert multiple values at runtime. 372 22:07.710 --> 22:08.730 123. 373 22:08.760 --> 22:09.660 Let me try. 374 22:09.690 --> 22:10.020 Okay. 375 22:10.050 --> 22:11.670 Three rows are inserted. 376 22:12.390 --> 22:14.460 Now let me refresh again. 377 22:18.390 --> 22:20.130 See two two tables are there. 378 22:20.130 --> 22:20.370 Right. 379 22:20.400 --> 22:21.870 Two two tables are there. 380 22:22.020 --> 22:23.370 Now I am creating. 381 22:23.370 --> 22:26.970 There is no primary key and foreign key relationship are there. 382 22:27.120 --> 22:29.250 So now we can drop the tables. 383 22:29.250 --> 22:35.040 Right now we can drop the tables right now both the tables are available right over here. 384 22:35.040 --> 22:36.090 You can see. 385 22:37.110 --> 22:44.410 Let me try select star from EMP and select star from dept. 386 22:47.620 --> 22:50.590 So if you see both the tables are available now. 387 22:50.590 --> 22:52.900 Now I am executing this procedure. 388 22:53.650 --> 22:56.620 See procedure executed successfully. 389 22:57.280 --> 22:59.530 Now if I go to this database. 390 22:59.530 --> 23:03.490 If I go to this database, if I do refresh. 391 23:06.190 --> 23:07.150 There is no table. 392 23:07.180 --> 23:07.780 See. 393 23:11.230 --> 23:15.520 We can we cannot write the one by one drop statement. 394 23:15.550 --> 23:15.880 Right. 395 23:15.880 --> 23:24.370 If we have 1000 table and we want to drop, or from 1000 table, we want to drop only 5000 table or 396 23:24.400 --> 23:28.000 sorry 500 table, then we cannot drop it one by one, right. 397 23:28.510 --> 23:30.340 We cannot drop it one by one. 398 23:30.340 --> 23:36.640 So for that purpose you need to create a store procedure like this. 399 23:38.320 --> 23:40.630 So it is a real time scenario right? 400 23:45.090 --> 23:45.480 Beautiful. 401 23:47.970 --> 23:51.180 Now let's see one more example. 402 23:51.210 --> 23:55.200 What is the oh yes. 403 23:56.700 --> 23:57.240 Question. 404 23:58.110 --> 23:58.410 Oh, yes. 405 23:58.410 --> 23:58.680 Yes. 406 23:59.580 --> 24:02.460 Um, let's say that, as you said a minute. 407 24:04.290 --> 24:04.800 Okay. 408 24:04.800 --> 24:12.150 So SP execute is something like it is a system stored procedure used to run one or multiple SQL statements 409 24:12.150 --> 24:13.650 stored inside a string. 410 24:13.830 --> 24:16.860 It supports a input and output parameters. 411 24:17.040 --> 24:21.720 So you can execute the query by using simple this statement. 412 24:22.350 --> 24:28.590 And in our case both of these query will give us the same result. 413 24:31.470 --> 24:36.810 But this is very rarely used some kind of real time scenario. 414 24:36.840 --> 24:43.740 I think we can say drop all the tables at one time, then another kind of thing we want to do. 415 24:43.770 --> 24:51.390 We can create a dynamic SQL and use a dynamic SQL concept, but you have to structure your query every 416 24:51.420 --> 24:51.840 time. 417 24:51.840 --> 25:01.380 That is little difficult in dynamic SQL to structure a query at runtime or while designing a query. 418 25:08.250 --> 25:08.820 Yes. 419 25:09.300 --> 25:10.590 Can you come down, please? 420 25:12.330 --> 25:13.320 Uh, why? 421 25:13.320 --> 25:15.150 This is in a different color. 422 25:15.180 --> 25:15.690 Red color. 423 25:15.720 --> 25:18.150 Usually select statements which you see it in. 424 25:18.780 --> 25:19.200 Oh, yeah. 425 25:19.230 --> 25:21.450 Actually there is some problem over here. 426 25:21.480 --> 25:25.350 See, now it is proper, right? 427 25:26.250 --> 25:27.330 But still it is executing. 428 25:27.360 --> 25:29.610 How come it is still. 429 25:29.610 --> 25:30.990 It is executing because. 430 25:31.020 --> 25:34.230 See, this is why it is dynamic SQL. 431 25:34.260 --> 25:37.590 See over here also how we are defining our statement. 432 25:37.620 --> 25:39.870 See this is in red color right. 433 25:39.900 --> 25:41.610 Drop table is in red color. 434 25:42.990 --> 25:44.850 Drop table is in red color. 435 25:44.880 --> 25:47.730 Then over here enter six. 436 25:47.760 --> 25:52.050 Select statement is in red color and then black color. 437 25:52.050 --> 25:54.510 This is not a normal Select statement right. 438 25:55.440 --> 25:57.690 Like this one's blue. 439 25:57.960 --> 26:04.110 So because of this is dynamic SQL we have to form our query in a single quote. 440 26:04.110 --> 26:09.300 And because of single quote it is showing in a different color. 441 26:09.330 --> 26:11.010 See now the color is gone. 442 26:12.570 --> 26:14.160 Now the red color is gone. 443 26:14.160 --> 26:16.140 But I think this will not work. 444 26:17.550 --> 26:23.880 See procedure or function as expects parameter statement which was not supplied. 445 26:26.970 --> 26:32.550 So that is why we need to form our query in single quotation mark. 446 26:34.830 --> 26:35.310 Got it.