{"id":2143,"date":"2020-07-22T22:18:11","date_gmt":"2020-07-22T22:18:11","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2143"},"modified":"2020-07-22T22:23:00","modified_gmt":"2020-07-22T22:23:00","slug":"union-en-store-procedure","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2143","title":{"rendered":"Union en Store procedure"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP__D.png\" alt=\"\" class=\"wp-image-2144\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Usando Union en Store Procedures SQL Server<\/h2>\n\n\n\n<p>En este art\u00edculo se muestran procedimientos almacenados en los que se han utilizado la cl\u00e1usula Union del Select para mostrar un reporte que incluye totales de un campo mostrado.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p><strong>Utilizando la base de datos Northwind<\/strong><br>use Northwind<br>go<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Crear un procedimiento almacenado que dado un a\u00f1o permita generar un informe de los pedidos realizados por un empleado ese a\u00f1o, totalizando el monto de sus operaciones ese a\u00f1o.<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spPedidosPorAnioEmpleado<br>(<br>@CodigoEmpleado int,<br>@Anio int<br>)<br>with encryption<br>As<br>Select<br>Str(O.OrderID) As &#8216;N\u00ba Orden&#8217;,<br>Str(YEAR(O.OrderDate)) As &#8216;A\u00f1o&#8217;,<br>Format(Sum((D.Quantity * D.UnitPrice)*(1 &#8211; D.Discount)),&#8217;###,##0.00&#8242;)<br>As &#8216;Monto Orden&#8217;<br>from Orders As O<br>join Employees As E on O.EmployeeID = E.EmployeeID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>where YEAR(O.OrderDate) = @Anio and O.ShippedDate is not null<br>and E.EmployeeID = @CodigoEmpleado<br>Group by O.OrderID, YEAR(O.OrderDate)<br><strong>union<\/strong><br>select &#8216;Total anual&#8217;, \u00bb,<br>Format(Sum((D.Quantity * D.UnitPrice)*(1 &#8211; D.Discount)),&#8217;###,##0.00&#8242;) As &#8216;Monto Orden&#8217;<br>from Orders As O<br>join Employees As E on O.EmployeeID = E.EmployeeID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>where YEAR(O.OrderDate) = @Anio and O.ShippedDate is not null<br>and E.EmployeeID = @CodigoEmpleado<br>Group by YEAR(O.OrderDate)<br>order by A\u00f1o desc<br>go<\/p>\n\n\n\n<p><strong>Mostrar el empleado con c\u00f3digo 1 en el a\u00f1o 1996<\/strong><\/p>\n\n\n\n<p>Exec spPedidosPorAnioEmpleado 1, 1996<br>go<br>La imagen muestra el resultado.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_01.png\" alt=\"\" class=\"wp-image-2145\" width=\"621\" height=\"1204\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_01.png 420w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_01-155x300.png 155w\" sizes=\"auto, (max-width: 621px) 100vw, 621px\" \/><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Teniendo un empleado, crear un procedimiento donde se genere un informe de todos los pedidos realizados con el total de cada uno, totalizando el monto al final de las ventas de cada a\u00f1o.<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spPedidosPorAnioEmpleadoTotal<br>(<br>@CodigoEmpleado int<br>)<br>with encryption<br>As<br>Select Str(O.OrderID) As &#8216;N\u00ba Orden&#8217;,<br>YEAR(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>Format(Sum((D.Quantity * D.UnitPrice)*(1 &#8211; D.Discount)),&#8217;###,##0.00&#8242;) As &#8216;Monto Orden&#8217;<br>from Orders As O<br>join Employees As E on O.EmployeeID = E.EmployeeID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>where O.ShippedDate is not null<br>and E.EmployeeID = @CodigoEmpleado<br>Group by O.OrderID, YEAR(O.OrderDate)<br>union<br>select &#8216;Total anual&#8217;, year(O.OrderDate) ,<br>Format(Sum((D.Quantity * D.UnitPrice)*(1 &#8211; D.Discount)),&#8217;###,##0.00&#8242;) As &#8216;Monto Orden&#8217;<br>from Orders As O<br>join Employees As E on O.EmployeeID = E.EmployeeID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>where O.ShippedDate is not null<br>and E.EmployeeID = @CodigoEmpleado<br>Group by YEAR(O.OrderDate)<br>order by A\u00f1o<br>go<\/p>\n\n\n\n<p><strong>Mostrar los resultados para el empleado con c\u00f3digo 2<\/strong><br>Exec spPedidosPorAnioEmpleadoTotal 2<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_02.png\" alt=\"\" class=\"wp-image-2146\" width=\"648\" height=\"1275\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_02.png 430w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_02-152x300.png 152w\" sizes=\"auto, (max-width: 648px) 100vw, 648px\" \/><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Mostrar el detalle y total de una orden espec\u00edfica<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spDetalleOrdenConTotal<br>(<br>@NumeroOrden int<br>)<br>with encryption<br>As<br>Select Str(O.OrderID) As &#8216;N\u00ba Orden&#8217;,<br>STR(D.ProductID) As &#8216;C\u00f3d. Producto&#8217;, P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>STR(D.Quantity) As &#8216;Cantidad&#8217;, STR(D.UnitPrice) As &#8216;Precio&#8217;,<br>&#8216;Importe&#8217; = D.Quantity * D.UnitPrice<br>from Orders As O<br>join customers As C on O.CustomerID= C.CustomerID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>join Products As P on D.ProductID = P.ProductID<br>where O.OrderID = @NumeroOrden<br>Group by O.OrderID, D.ProductID, P.ProductName, D.Quantity, D.UnitPrice<br>union<br>select &#8216;Total Orden&#8217;, \u00bb,\u00bb,\u00bb,\u00bb,<br>Format(Sum((D.Quantity * D.UnitPrice)),&#8217;###,##0.00&#8242;) As &#8216;Importe&#8217;<br>from [Order Details] As D<br>join Orders As O on D.OrderID = O.OrderID<br>join Customers As C on O.CustomerID = C.CustomerID<br>where O.OrderID = @NumeroOrden<br>Group by O.OrderID<br>go<\/p>\n\n\n\n<p><strong>Para mostrar el detalle de la orden 10248<\/strong><br>Execute spDetalleOrdenConTotal 10248<br>go<br>La imagen muestra el resultado.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_03.png\" alt=\"\" class=\"wp-image-2147\" width=\"886\" height=\"204\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_03.png 894w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_03-300x69.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/07\/ManualSQL_Union_en_SP_03-768x177.png 768w\" sizes=\"auto, (max-width: 886px) 100vw, 886px\" \/><\/figure>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Usando Union en Store Procedures SQL Server En este art\u00edculo se muestran procedimientos almacenados en los que se han utilizado la cl\u00e1usula Union del Select para mostrar un reporte que incluye totales de un campo mostrado.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2143\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2144,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,233],"tags":[18,51,242,61],"class_list":["post-2143","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-sp","tag-create-procedure","tag-select","tag-store-procedure","tag-union","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2143","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=2143"}],"version-history":[{"count":2,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2143\/revisions"}],"predecessor-version":[{"id":2151,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2143\/revisions\/2151"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2144"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}