{"id":335,"date":"2017-09-19T23:30:38","date_gmt":"2017-09-19T23:30:38","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=335"},"modified":"2020-07-17T21:51:04","modified_gmt":"2020-07-17T21:51:04","slug":"335","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=335","title":{"rendered":"Funciones definidas por el usuario FDU"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_FDU_D.png\" alt=\"\" class=\"wp-image-946\" width=\"226\" height=\"244\"\/><\/figure>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-flow wp-block-group-is-layout-flow\">\n<h3 class=\"wp-block-heading\"><strong>FUNCIONES DEFINIDAS POR EL USUARIO<\/strong><\/h3>\n\n\n\n<p>Las Funciones definidas por el usuario son rutinas que aceptan par\u00e1metros de manera opcional, &nbsp;realizan acciones y devuelven el resultado como un valor o como una tabla.<br>El valor devuelto puede ser un valor escalar \u00fanico o un conjunto de resultados.<\/p>\n\n\n\n<p><strong>Las Funciones Definidas por el usuario explicadas en este art\u00edculo son:<\/strong><br>1. Las que retornan UN VALOR &#8211; InLine,se utilizan en otras instrucciones<br>2. Las que retornan UNA TABLA<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Para crear las que devuelven un valor.<\/strong><\/p>\n\n\n\n<p>Create function Esquema.NombreFuncion([Par\u00e1metros]) Returns TipoDato<br>As<br>Begin<br>Instrucciones&#8230;.<br>Return ValorRetornado<br>End<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Para crear las FDU que retornan UNA TABLA<\/strong><\/p>\n\n\n\n<p>Create function Esquema.NombreFuncion([Par\u00e1metros]) Returns Table<br>As<br>Return (Select&#8230;.)<br>go<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\">Ejercicios<\/h3>\n\n\n\n<p><strong>Usando Northwind<\/strong><br>use Northwind<br>go<\/p>\n\n\n\n<p><strong>FDU que retorne los productos con tengan mas Unidades en Orden que Stock actual<\/strong><br>Create function fduProductoCompraUrgente () returns Table<br>As<br>Return (select * from Products where UnitsOnOrder &gt; UnitsInStock)<br>go<br><strong>Para usar la funci\u00f3n creada<\/strong><br>select * from fduProductoCompraUrgente()<br>go<\/p>\n\n\n\n<p><strong>Listar de Pedidos de un cliente, escribir el nombre del cliente<\/strong><br><strong>Primero: buscar el c\u00f3digo del cliente.<\/strong><br>Ejemplo \u00abUniversidad SQL\u00bb<br>select CustomerID from Customers &nbsp;where CompanyName = &#8216;Universidad SQL&#8217;<br>go<br><strong>Se comprueba que no existe el cliente<\/strong><br>Para el cliente \u00abAntonio Moreno Taquer\u00eda\u00bb<br>select CustomerID from Customers &nbsp;where CompanyName = &#8216;Antonio Moreno Taquer\u00eda&#8217;<br>go<br>Resultado: ANTON<\/p>\n\n\n\n<p>Para las \u00d3rdenes del Cliente se puede usar una sub consulta. (<a href=\"http:\/\/www.manualsqlserver.com\/?p=219\">Ver Sub consultas<\/a>)<br>SELECT * FROM Orders &nbsp;where &nbsp; CustomerID = (select CustomerID from Customers<br>where CompanyName = &#8216;Antonio Moreno Taquer\u00eda&#8217;)<br>go<\/p>\n\n\n\n<p><strong>En una FDU<\/strong><br>Create function fduOrdenesPorCliente(@Cliente nvarchar(40)) Returns Table<br>As<br>Return (SELECT * FROM Orders<br>where CustomerID = (select CustomerID from Customers<br>where CompanyName =@Cliente ))<br>go<\/p>\n\n\n\n<p><strong>Usando la FDU anterior, las \u00d3rdenes para &#8216;Antonio Moreno Taquer\u00eda&#8217;<\/strong><br>select * from fduOrdenesPorCliente(&#8216;Antonio Moreno Taquer\u00eda&#8217;)<br>go<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><strong>Productos con precio mayor a valor ingresado<\/strong><br>Create function fduProductosPrecioHaciaArriba(@Precio Numeric(9,2)) Returns Table<br>As<br>Return (select * from Products where UnitPrice &gt; @Precio)<br>go<br><strong>Usar la FDU<\/strong><br>select * from fduProductosPrecioHaciaArriba(80)<br>go<\/p>\n\n\n\n<p><strong>Pedidos de un cliente en un rango de fechas<\/strong><br>Create function fduOrdenesPorClienteRangoFechas(@Cliente nvarchar(40), @FechaInicial Date,<br>@FechaFinal Date) Returns Table<br>As<br>Return (SELECT * FROM Orders<br>where CustomerID = (select CustomerID from Customers where CompanyName =@Cliente )<br>and (OrderDate &gt;= @FechaInicial and OrderDate &lt;= @FechaFinal)<br>)<br>go<br><strong>Usar la FDU fduOrdenesPorClienteRangoFechas<\/strong><br>select * from fduOrdenesPorClienteRangoFechas(&#8216;Antonio Moreno Taquer\u00eda&#8217;,&#8217;01\/06\/1997&#8242;,&#8217;31\/12\/1997&#8242;)<br>go<\/p>\n\n\n\n<p><strong>Listado de las categor\u00edas y el valor del Stock de cada una<\/strong><br><strong>Valor del Stock para la categoria 1<\/strong><br>select SUM(UnitPrice * UnitsInStock) from Products where CategoryID = 1<br>go<br><strong>La FDU para calcular el valor del stock<\/strong><br>Create function fduValorStockPorCategoria(@CodigoCategoria int)<br>Returns Numeric(9,2)<br>As<br>Begin<br>&#8212; Variable para capturar el Valor<br>Declare @ValorTotalStock Numeric(9,2)<br>set @ValorTotalStock = (select SUM(UnitPrice * UnitsInStock)<br>from Products where CategoryID = @CodigoCategoria )<br>Return @ValorTotalStock<br>End<br>go<br><strong>Usar la FDU fduValorStockPorCategoria<\/strong><br>select C.CategoryID As &#8216;C\u00f3digo&#8217;, C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>dbo.fduValorStockPorCategoria(C.CategoryID) As &#8216;Valor Stock&#8217;<br>from Categories As C &nbsp;order by [Valor Stock] desc<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_FDU_01.png\" alt=\"\" class=\"wp-image-948\" width=\"729\" height=\"376\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_FDU_01.png 552w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2017\/09\/ManualSQL_FDU_01-300x155.png 300w\" sizes=\"auto, (max-width: 729px) 100vw, 729px\" \/><\/figure><\/div>\n\n\n\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-337\" src=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_FDU_01-300x155.png\" alt=\"\" width=\"422\" height=\"218\"><br>Costo consultando el Plan de Ejecuci\u00f3n: 0.0146903<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_02-1024x540.png\" alt=\"\" class=\"wp-image-949\" width=\"818\" height=\"431\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_02-1024x540.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_02-300x158.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_02-768x405.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_02.png 1097w\" sizes=\"auto, (max-width: 818px) 100vw, 818px\" \/><\/figure><\/div>\n\n\n\n<p>El mismo resultado usando Subconsultas<br>select C.CategoryID As &#8216;C\u00f3digo&#8217;, C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>(select SUM(UnitPrice * UnitsInStock)<br>from Products As P where P.CategoryID = C.CategoryID)<br>As &#8216;Valor Stock&#8217;<br>from Categories As C<br>order by [Valor Stock] desc<br>go<br>Costo consultando el Plan de Ejecuci\u00f3n: 0.0196404<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_03-1024x525.png\" alt=\"\" class=\"wp-image-950\" width=\"851\" height=\"436\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_03-1024x525.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_03-300x154.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_03-768x394.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_FDU_03.png 1244w\" sizes=\"auto, (max-width: 851px) 100vw, 851px\" \/><\/figure><\/div>\n\n\n\n<p>Se puede ver que la FDU es m\u00e1s r\u00e1pida que la sub consulta.<br><script>&amp;lt;br \/>      (adsbygoogle = window.adsbygoogle || []).push({});&amp;lt;br \/> <\/script><\/p>\n\n\n\n<p><strong>Eliminar una FDU<\/strong><br>Drop function fduOrdenesPorCliente<br>go<\/p>\n\n\n\n<p class=\"has-text-color has-background has-very-dark-gray-color has-very-light-gray-background-color\"><strong>Importante<\/strong>: Se recomienda tener cuidado en la eliminaci\u00f3n de una FDU y en general de cualquier objeto, este puede estar referenciado en alg\u00fan otro script y si se elimina cuasar\u00e1 problema en el sistema.<\/p>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>FUNCIONES DEFINIDAS POR EL USUARIO Las Funciones definidas por el usuario son rutinas que aceptan par\u00e1metros de manera opcional, &nbsp;realizan acciones y devuelven el resultado como un valor o como una tabla.El valor devuelto puede ser un valor escalar \u00fanico o un conjunto de resultados. Las Funciones Definidas por el usuario explicadas en este art\u00edculo &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=335\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":946,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[236,8],"tags":[29,67,53],"class_list":["post-335","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-udffunciones","category-programacion","tag-fdu","tag-funciones-deficidas-por-el-usuario","tag-sqlserver","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/335","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=335"}],"version-history":[{"count":7,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/335\/revisions"}],"predecessor-version":[{"id":2127,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/335\/revisions\/2127"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/946"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=335"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=335"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}