Inline table valued functions in sql server Part 31 — Transcript

Full transcript

1. 0:00hello welcome to preim Technologies I'm
2. 0:02wenet this is part 31 of SQL server in
3. 0:05this session we'll learn about creating
4. 0:07and calling inline table valued
5. 0:09functions where do we use inline table
6. 0:12valued functions now before continuing
7. 0:14with the session I strongly recommend to
8. 0:15watch Part 30 of this video
9. 0:18series in part 30 we have seen how to
10. 0:21create and call Scala user defined
11. 0:24functions in this part we'll learn about
12. 0:27inline table valued functions we know
13. 0:29that a scalar function returns a scalar
14. 0:32value a single value whereas an inline
15. 0:35table valued function returns a table
16. 0:39now let's look at an example of creating
17. 0:41an inline table valued function now if
18. 0:44you look at this I've got an employee
19. 0:46table which has got ID name date of
20. 0:48birth gender and Department ID columns
21. 0:51now I want you to write a function that
22. 0:53can give me the output that you can see
23. 0:55on the right hand side you know the
24. 0:57function should return employees by
25. 0:59gender for example if I passed your
26. 1:01function gender as male I want only the
27. 1:04male employees to be returned and along
28. 1:06the same lines if I pass the gender as
29. 1:08female and I only want the female
30. 1:10employees to be returned okay and to do
31. 1:13that we use the create function
32. 1:15statement as we have used it to create
33. 1:18Scala function okay so create if you
34. 1:21look at the you know implementation of
35. 1:23the function here it's very simple to
36. 1:25understand so we use the create function
37. 1:27statement so create function function
38. 1:29name name and FN underscore employees by
39. 1:33gender is the name of the function
40. 1:35because we are going to return employees
41. 1:36by gender and if you look at this
42. 1:38obviously this function needs a
43. 1:40parameter at gender of type NW care
44. 1:44whose length is 10 okay since gender is
45. 1:46a string we use nare data type and then
46. 1:50this function returns table okay so this
47. 1:54is the parameter that we are passing
48. 1:55into this function at gender and this
49. 1:57function returns stable if you remember
50. 2:00in part 30 you know where we spoke about
51. 2:02scalar user defined functions Scala
52. 2:05function return a scalar data type for
53. 2:07example maybe nare care or integer or
54. 2:10date time you know a Scala value but we
55. 2:13want this function now to return a list
56. 2:15of employees by gender okay so obviously
57. 2:18we want this function to return a table
58. 2:20so we are specifying table as the return
59. 2:23type from this function using the
60. 2:26returns
61. 2:26keyword and then we are using as keyword
62. 2:31and return look at this for an inline
63. 2:34table valued function we are not using
64. 2:36the begin and end block if you remember
65. 2:39for the Scala user defined function we
66. 2:42had begin and end block the function
67. 2:44body was enclosed within that block but
68. 2:47for an inline table valued function it's
69. 2:49a compile time error to have um you know
70. 2:53basically the begin and end block you
71. 2:55cannot have it for an inline table
72. 2:57valued function so immediately after the
73. 3:00ask keyword you specify the return and
74. 3:03within brackets you specify your select
75. 3:05statement okay so what do we want here
76. 3:08we want ID name date of birth gender and
77. 3:10Department ID columns so specify them in
78. 3:12your select list from TBL employees
79. 3:15table where gender equals at gender
80. 3:20parameter whatever the user is going to
81. 3:22pass in okay so we are using that
82. 3:24parameter there and we are enclosing
83. 3:26that entire select statement in
84. 3:28parenthesis okay just I mean even if you
85. 3:30don't indicate that we don't get an
86. 3:32error but but it is more readable now
87. 3:34after whatever is present in the in this
88. 3:36parenthesis is completed return the
89. 3:39output of the execution you know of the
90. 3:42execution result of the select statement
91. 3:45back to the calling application so when
92. 3:46somebody calls your function return
93. 3:49whatever your select statement is
94. 3:51selecting
95. 3:52here okay so if you notice there are
96. 3:55three points to keep in mind when we
97. 3:57compare inline table valued functions
98. 3:59with Scala
99. 4:00um user defined functions now with Scala
100. 4:03functions I mean with inline table
101. 4:04valued functions we specify table as the
102. 4:07return type whereas for scalar functions
103. 4:10we specify any of the scalar data types
104. 4:13like integer andare Etc okay and for
105. 4:17scalar functions you know here the
106. 4:19function body is not enclosed between
107. 4:21begin and end block the structure of the
108. 4:23table this is very important now look at
109. 4:26this you're telling your function it's
110. 4:27going to return a table but what are the
111. 4:30columns that are going to be present in
112. 4:32the in that table you know basically the
113. 4:34structure of the table we are not
114. 4:36defining that here okay so the structure
115. 4:39of the table will be determined by the
116. 4:42select statement that you have so
117. 4:43obviously the table that this function
118. 4:45is going to return is going to have 1 2
119. 4:473 4 five columns because your select
120. 4:50query has five columns within its select
121. 4:53list okay so the structure of the table
122. 4:56that gets returned is determined by the
123. 4:58select statement within the function
124. 5:01body okay but later in the next session
125. 5:04we'll be talking about multi-statement
126. 5:06table valued function there we have the
127. 5:08luxury of specifying the structure of
128. 5:10the table within the return after the
129. 5:13return keyword we'll talk about that in
130. 5:15the next session okay so let's look
131. 5:18quickly you know creating how to create
132. 5:20this function so if you look at this I
133. 5:23have this TBL employees table which is
134. 5:25called The Columns that we have seen on
135. 5:26the slide and to create the function we
136. 5:28use the create function keyword the name
137. 5:31of the function and then obviously the
138. 5:33parameter that we want to pass along
139. 5:35with its data type returns table you
140. 5:38specify the ask keyword return and your
141. 5:41select statement within brackets okay so
142. 5:46let's execute this so when I execute
143. 5:48command complet it successfully okay so
144. 5:51where did this function get created if
145. 5:53you look at the context we are executing
146. 5:54this query in the sample database so
147. 5:56once I expand sample database go to
148. 5:58programmability and then to functions
149. 6:01there you should see table valued
150. 6:03functions in part 30 when we created
151. 6:05Scala function that was stored within
152. 6:08the Scala valued functions table but
153. 6:10this is a table valued function an
154. 6:12inline table valued function so that
155. 6:15gets stored in this table valued
156. 6:17functions folder if you can't see that
157. 6:19just refresh it and expand the plus sign
158. 6:22you should see the function that we have
159. 6:24just created which is FN employees by
160. 6:27gender okay
161. 6:33now let us see how to call this function
162. 6:35so obviously to call this function you
163. 6:37know we use the syntax select star from
164. 6:40the function name and then pass whatever
165. 6:43parameters you have to pass to that
166. 6:44function now this function returns a
167. 6:47table so treat as if it is a table okay
168. 6:51now if we want to select from a table we
169. 6:54will say select star from that table
170. 6:56name similarly this is an inline table
171. 6:58valued function
172. 7:00just issue a select statement against
173. 7:01this inline table valued function as if
174. 7:05you would issue a select statement
175. 7:06against the table so select star from
176. 7:09that function name okay so let's see how
177. 7:14to execute that so select star from I
178. 7:17want all the male employees so I'm
179. 7:18passing in mail and when we execute this
180. 7:21query we only get the male employees
181. 7:23back and along the same lines if I want
182. 7:25female employees I just pass female and
183. 7:28execute the query so I I only get the
184. 7:30female
185. 7:32employees okay that's the syntax that we
186. 7:34use to execute I mean to call the
187. 7:36function now if you want you can even
188. 7:40say use the we Clause you know from with
189. 7:42a table we can use the we Clause even
190. 7:45with the function you can use the we
191. 7:46Clause okay this is a inline table
192. 7:49valued function we get a table back so
193. 7:51treat as if it's a table and you can
194. 7:53just say where name is equal to maybe
195. 7:57Pam I only get Pam's record now when we
196. 8:00execute this all right okay so where can
197. 8:04we actually use this inline table valued
198. 8:07function okay um you can use I mean
199. 8:10inline table valued functions can be
200. 8:12used to achieve the functionality of
201. 8:14parameterized views now what are what is
202. 8:17a view and what are parameterized views
203. 8:19we haven't spoken about them yet we'll
204. 8:21be talking about them in our later
205. 8:22sessions so until then don't worry about
206. 8:25this point but just keep in mind that
207. 8:27inline table valued functions are an
208. 8:29exceptional replacement for
209. 8:31parameterized views which we will talk
210. 8:34about in detail in a later session okay
211. 8:36and another place where you can actually
212. 8:38use this inline table valued function
213. 8:40you know is that you can actually use it
214. 8:43in joints as well you know we know that
215. 8:46the in line table valued function
216. 8:47returns a table so the table returned by
217. 8:50the table valued function can be used in
218. 8:52Joins with other tables okay so if you
219. 8:55look at this when we execute this query
220. 8:57select star from function employees by
221. 8:59agenda uh we got this result back since
222. 9:02I'm passing in mail as the parameter
223. 9:05value I get all male employees like this
224. 9:07that you can see on the slide now if you
225. 9:09look at the output we have got
226. 9:11Department ID in The Returned table by
227. 9:13this function okay now what I want you
228. 9:16to do is I want you to join this return
229. 9:18table with Department's table so that
230. 9:22you can give me the output that you can
231. 9:23see here I want the name of the employee
232. 9:26their gender and their Department name
233. 9:29if you look at the table returned by the
234. 9:31function you don't have Department name
235. 9:34in that table so obviously you will have
236. 9:36to join the return the table with the
237. 9:39Department's table okay and if you're
238. 9:42new to joints we have already spoken
239. 9:45extensively about joints in part 12 and
240. 9:4713 of this video series please check
241. 9:49those parts okay now if you look at the
242. 9:52query here it's pretty simple just like
243. 9:54how you join a table with another table
244. 9:57we do exactly the same thing
245. 10:00okay so first of all what are the
246. 10:01columns that we want we want the name
247. 10:03gender and Department name so you
248. 10:05specify them in your select list select
249. 10:07name gender Department name from you
250. 10:11specify your function name passing it
251. 10:13the parameter that you want here we want
252. 10:16to pass in mail because we want the male
253. 10:18employees so function name male
254. 10:21employees and then give it an alas just
255. 10:23like how you would give an alas to a
256. 10:25table okay e for the function
257. 10:30and then join TBL Department D on
258. 10:35department. ID with employee. Department
259. 10:40ID okay that's about it so you can even
260. 10:43use the table that is returned by an
261. 10:45inline table valued function in a
262. 10:48join let's
263. 10:50see the query so here you have that
264. 10:53select name gender Department name from
265. 10:55the function give it an alas join with
266. 10:58TBL Department give it another alas D on
267. 11:02department. ID equals employee.
268. 11:05Department ID so when we execute that we
269. 11:07get the output that we
270. 11:15expect on this slide you can find
271. 11:17resources for asp.net and cop interview
272. 11:19questions that's it for today thank you
273. 11:21for listening have a great day