{"id":2397,"date":"2020-12-29T20:20:04","date_gmt":"2020-12-29T20:20:04","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2397"},"modified":"2020-12-29T20:20:05","modified_gmt":"2020-12-29T20:20:05","slug":"cursores-en-store-procedure-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2397","title":{"rendered":"Cursores en Store Procedure SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP__D.png\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP__D.png\" alt=\"\" class=\"wp-image-2398\"\/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Usando Cursores en Store procedures<\/h2>\n\n\n\n<p>Los cursores en SQL Server permiten almacenar en memoria un conjunto de registros resultado de una instrucci\u00f3n Select, el objetivo principal del uso de los cursores es recorrer los registros del conjunto de resultados y realizar alg\u00fan proceso con cada uno.<\/p>\n\n\n\n<!--more-->\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>En este art\u00edculo se muestran ejercicios usando cursores que pueden dar al lector una idea para llenar o crear alg\u00fan reporte necesario, adem\u00e1s de como usarlos cuando la instrucci\u00f3n select del cursor es din\u00e1mica. Para esto  se han inclu\u00eddo cursores en procedimientos almacenados.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Para m\u00e1s informaci\u00f3n ver:<\/strong><br><a href=\"https:\/\/manualsqlserver.com\/?p=343\" target=\"_blank\" rel=\"noreferrer noopener\">Cursores en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=2342\" target=\"_blank\" rel=\"noreferrer noopener\">Usando Cursores en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=1630\" target=\"_blank\" rel=\"noreferrer noopener\">Cursores con variables tipo tabla<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=2078\" target=\"_blank\" rel=\"noreferrer noopener\">Cursores en SP para llenar tablas<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=335\" target=\"_blank\" rel=\"noreferrer noopener\">Funciones definidas por el usuario<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=314\" target=\"_blank\" rel=\"noreferrer noopener\">Procedimientos almacenados<\/a><\/p>\n\n\n\n<p><strong>Usando la base de datos Northwind<br><\/strong>use Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>En este ejercicio se muestran s\u00f3lo dos productos por categor\u00eda, seleccionando los que tienen mayor Stock.<\/strong><br>Declare cursorCategorias cursor for select CategoryID, CategoryName from Categories<br>Open cursorCategorias<br>Declare @CodigoCategoria int, @NombreCategoria nvarchar(15)<br>Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria<br>Declare @DosProductosPorCategoria table<br>( Codigo int, Descripcion nvarchar(40), Precio Numeric(9,2), Categoria nvarchar(15))<br>While (@@FETCH_STATUS = 0)<br>Begin<br>&#8211;Crear el cursor para leer dos productos de la categor\u00eda actual<br>Declare cursorProductosPorCategoria cursor for<br>select Top 2 ProductID, ProductName, UnitPrice<br>from Products As P where CategoryID = @CodigoCategoria order by P.UnitsInStock desc<br>Open cursorProductosPorCategoria<br>Declare @CodigoProducto int, @NombreProducto nvarchar(40),@Precio Decimal<br>Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio<br>While (@@FETCH_STATUS = 0)<br>Begin<br>insert into @DosProductosPorCategoria<br>values (@CodigoProducto , @NombreProducto ,@Precio,@NombreCategoria)<br>Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio<br>End<br>Close cursorProductosPorCategoria<br>Deallocate cursorProductosPorCategoria<br>Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria<br>End<br>close cursorCategorias<br>Deallocate cursorCategorias<br>select Categoria, Codigo, Descripcion, Precio from @DosProductosPorCategoria<br>go<br><strong>El resultado se muestra en la siguiente imagen<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_01.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_01.png\" alt=\"\" class=\"wp-image-2399\" width=\"672\" height=\"563\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_01.png 608w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_01-300x252.png 300w\" sizes=\"auto, (max-width: 672px) 100vw, 672px\" \/><\/a><figcaption>Dos productos por categor\u00eda<\/figcaption><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Mostrar n productos por categoria, el valor ser\u00e1 un par\u00e1metro en un SP, este ejercicio va a mostrar los productos com mayor precio. Note que la instrucci\u00f3n select del cursor se debe construir y luego usar el procedimiento almacenado sp_executesql para llenar el cursor.<\/strong><br>Create or alter procedure spNProductosMasCarosPorCategoria<br>(@Cantidad int)<br>As<br>Declare cursorCategorias cursor for select CategoryID, CategoryName from Categories<br>Open cursorCategorias<br>Declare @CodigoCategoria int, @NombreCategoria nvarchar(15)<br>Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria<br>Declare @DosProductosPorCategoria table<br>( Codigo int, Descripcion nvarchar(40), Precio Numeric(9,2), Categoria nvarchar(15))<br>While (@@FETCH_STATUS = 0)<br>Begin<br>Declare @InstruccionSelect nvarchar(500)<br>Set @InstruccionSelect =<br>&#8216;Declare cursorProductosPorCategoria cursor for<br>select Top &#8216; + Trim(Str(@Cantidad)) + &#8216; ProductID, ProductName, UnitPrice<br>from Products As P where CategoryID = &#8216; + Trim(Str(@CodigoCategoria)) + &#8216; order by P.UnitPrice desc&#8217;<br>Execute sp_executesql @InstruccionSelect<br>Open cursorProductosPorCategoria<br>Declare @CodigoProducto int, @NombreProducto nvarchar(40),@Precio Decimal<br>Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio<br>While (@@FETCH_STATUS = 0)<br>Begin<br>insert into @DosProductosPorCategoria<br>values (@CodigoProducto , @NombreProducto ,@Precio,@NombreCategoria)<br>Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio<br>End<br>Close cursorProductosPorCategoria<br>Deallocate cursorProductosPorCategoria<br>Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria<br>End<br>close cursorCategorias<br>Deallocate cursorCategorias<br>select * from @DosProductosPorCategoria<br>go<\/p>\n\n\n\n<p><strong>Ejecutando el SP con 4 productos por cada categor\u00eda.<br><\/strong>Exec spNProductosMasCarosPorCategoria 4<br>go<br><strong>El resultado se muestra en la siguiente imagen<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_02.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_02.png\" alt=\"\" class=\"wp-image-2400\" width=\"720\" height=\"991\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_02.png 591w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_02-218x300.png 218w\" sizes=\"auto, (max-width: 720px) 100vw, 720px\" \/><\/a><\/figure>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Crear un cursor que muestre las n \u00f3rdenes con mayor valor de los clientes. Para el calculo del total de la orden crear una Funci\u00f3n definida por el usuario.<\/strong><\/p>\n\n\n\n<p><strong>Creando una FDU para calcular el total de una orden<br><\/strong>Create or alter function dbo.fduCalculaTotalOrden(@NumeroOrden int)<br>Returns Numeric(9,2)<br>As<br>Begin<br>Declare @Total Numeric(9,2)<br>set @Total =<br>(<br>Select ROUND(Sum((D.Quantity * D.UnitPrice) * ( 1- D.Discount)),2)<br>from [Order Details] As D<br>where D.OrderID = @NumeroOrden<br>)<br>Return @Total<br>End<br>go<br><strong>Para mostrar el reporte requerido para un cliente. \u00d3rdenes para un cliente y sus totales, por ejemplo el cliente con c\u00f3digo ALFKI.<br><\/strong>select<br>C.CustomerID As &#8216;C\u00f3d. Cliente&#8217;,<br>C.CompanyName As &#8216;Cliente&#8217;,<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, Format(O.OrderDate,&#8217;dd\/MM\/yyyy&#8217;) As &#8216;Fecha&#8217;,<br>dbo.<strong>fduCalculaTotalOrden<\/strong>(O.OrderID) As Total<br>from Orders As O<br>join Customers As C on O.CustomerID = C.CustomerID<br>where O.CustomerID = &#8216;ALFKI&#8217;<br>order by Total desc<br>go<\/p>\n\n\n\n<p><strong>Ahora el procedimiento almacenado que permite mostrar las N \u00f3rdenes con mayor valor de los clientes<br><\/strong>Create or alter procedure spNOrdenesMasValorPorCliente<br>(@Cantidad int)<br>As<br>Declare cursorClientes cursor for select CustomerID, CompanyName from Customers<br>Open cursorClientes<br>Declare @CodigoCliente nchar(5), @NombreCliente nvarchar(40)<br>Fetch cursorClientes into @CodigoCliente, @NombreCliente<br>Declare @OrdenesPorCliente table<br>( Codigo nchar(5), Cliente nvarchar(40), Orden int, Fecha Date, Total Numeric(9,2))<br>While (@@FETCH_STATUS = 0)<br>Begin<br>Declare @InstruccionSelect nvarchar(500)<br>Set @InstruccionSelect =<br>(<br>&#8216;Declare cursorTopNOrdenesPorClienteMasValor cursor for<br>select Top &#8216;+ trim(STR(@Cantidad)) +<br>&#8216; O.OrderID, O.OrderDate,<br>dbo.fduCalculaTotalOrden(O.OrderID) As Total<br>from Orders As O<br>where O.CustomerID = &#8216; + char(39) + trim(@CodigoCliente) + char(39) +&#8217;<br>order by Total desc&#8217;<br>)<br>Execute sp_executesql @InstruccionSelect<br>Open cursorTopNOrdenesPorClienteMasValor<br>Declare @OrdenNumero int, @FechaOrden Date, @ImporteTotal Numeric(9,2)<br>Fetch cursorTopNOrdenesPorClienteMasValor into @OrdenNumero , @FechaOrden ,@ImporteTotal<br>While (@@FETCH_STATUS = 0)<br>Begin<br>insert into @OrdenesPorCliente<br>values<br>(@CodigoCliente, @NombreCliente,<br>@OrdenNumero , @FechaOrden ,@ImporteTotal)<br>Fetch cursorTopNOrdenesPorClienteMasValor into @OrdenNumero , @FechaOrden ,@ImporteTotal<br>End<br>Close cursorTopNOrdenesPorClienteMasValor<br>Deallocate cursorTopNOrdenesPorClienteMasValor<br>Fetch cursorClientes into @CodigoCliente, @NombreCliente<br>End<br>close cursorClientes<br>Deallocate cursorClientes<br>select * from @OrdenesPorCliente<br>go<\/p>\n\n\n\n<p><strong>Ejecutar el procedimiento para mostrar cuatro \u00f3rdenes con mayor valor.<br><\/strong>Execute spNOrdenesMasValorPorCliente 4<br>go<br><strong>El resultado se muestra en la siguiente imagen.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_03.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_03.png\" alt=\"\" class=\"wp-image-2401\" width=\"721\" height=\"776\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_03.png 741w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_03-279x300.png 279w\" sizes=\"auto, (max-width: 721px) 100vw, 721px\" \/><\/a><\/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 style=\"font-size:24px\"><strong>Procedimiento almacenado sp_executesql<\/strong><\/p>\n\n\n\n<p><strong>Permite ejecutar una instrucci\u00f3n SQL la que puede reusarse o que hay sido creada din\u00e1micamente. Esta instrucci\u00f3n SQL puede contener par\u00e1metros.<\/strong><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p><strong>En este ejercicio se crea una instrucci\u00f3n sencilla para listar los registros de la tabla Products.<\/strong><br>use Northwind<br>go<br>Declare @Instruccion nvarchar(200)<br>Set @Instruccion = &#8216;Select * from Products&#8217;<br>Execute sp_executesql @Instruccion<br>go<br><strong>El resultado se muestra en la siguiente imagen.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_04.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_04-1024x359.png\" alt=\"\" class=\"wp-image-2402\" width=\"765\" height=\"268\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_04-1024x359.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_04-300x105.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_04-768x270.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_04-795x279.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/12\/ManualSQL_CursoresEnSP_04.png 1265w\" sizes=\"auto, (max-width: 765px) 100vw, 765px\" \/><\/a><figcaption><strong>Productos listado con el uso de sp_executesql<\/strong><\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Usando Cursores en Store procedures Los cursores en SQL Server permiten almacenar en memoria un conjunto de registros resultado de una instrucci\u00f3n Select, el objetivo principal del uso de los cursores es recorrer los registros del conjunto de resultados y realizar alg\u00fan proceso con cada uno.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2397\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2398,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[232],"tags":[275,73,242],"class_list":["post-2397","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cursoressql","tag-cursor-en-sql-server","tag-declare-cursor","tag-store-procedure","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2397","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=2397"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2397\/revisions"}],"predecessor-version":[{"id":2403,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2397\/revisions\/2403"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2398"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}