{"id":1630,"date":"2020-05-23T21:44:37","date_gmt":"2020-05-23T21:44:37","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=1630"},"modified":"2020-07-17T15:46:23","modified_gmt":"2020-07-17T15:46:23","slug":"cursores-con-variables-tipo-tabla","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=1630","title":{"rendered":"Cursores con variables tipo tabla"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"278\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CursoresVariablesTipoTabla__D.png\" alt=\"\" class=\"wp-image-1631\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Cursor con variable tipo tabla en SQL Server<\/h2>\n\n\n\n<p>Los cursores guardan en memoria el resultado de una instrucci\u00f3n Select para luego poder recorrer los registros y poder con cada uno de ellos realizar alg\u00fan tipo de trabajo.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p><strong>Importante<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Los cursores ocupan mucha memoria, evitar en lo posible el uso de estos y si deciden usarlos la cantidad de registros y los campos del select deber\u00eda ser lo m\u00e1s selectiva posible.<\/li><li>Es recomendable guardar los datos del procesamiento del cursor en variables tipo tabla (<a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=522\" target=\"_blank\">Ver Variables tipo tabla<\/a>) o tablas temporales (<a href=\"https:\/\/manualsqlserver.com\/?p=424\" target=\"_blank\" rel=\"noreferrer noopener\">Ver tablas temporales<\/a>) e incluir estos dentro de un procedimiento almacenado para ser consumido desde las aplicaciones.<\/li><\/ul>\n\n\n\n<p>En este art\u00edculo se presentan ejercicios de cursores con el uso de variables tipo tabla.<\/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 class=\"has-medium-font-size\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Crear un cursor que muestre el c\u00f3digo del empleado y el n\u00famero de \u00f3rdenes de pedido emitidas<br><\/strong>Declare @EmpleadosOrdenes table<br>( EmpleadoCodigo nchar(5),<br>EmpleadoNombreCompleto nvarchar(100),<br>EmpleadoCantidadOrdenes int<br>)<br>&#8212; Definir las variables<br>declare @EmpleadoCodigo nchar(5)<br>declare @EmpleadoNombreCompleto nvarchar(100)<br>declare @EmpleadoCantidadOrdenes int<br>&#8212; Crear el cursor<br>declare cursorEmpleadosOrdenes cursor for<br>select Right(&#8216;0000&#8217; + LTrim(Str(E.EmployeeID)),5) As &#8216;C\u00f3digo&#8217;,<br>Empleado = E.LastName + Space(1) + E.FirstName,<br>count(O.OrderID) As &#8216;Cantidad&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeID = O.EmployeeID<br>group by E.EmployeeID, E.LastName + Space(1) + E.FirstName<br>&#8212; Abrir el cursor<br>open cursorEmpleadosOrdenes<br>&#8212; Leer los datos a las variables del primer registro<br>fetch next from cursorEmpleadosOrdenes<br>into @EmpleadoCodigo,@EmpleadoNombreCompleto, @EmpleadoCantidadOrdenes<br>while @@FETCH_STATUS =0 &#8212; recorrer el cursor<br>begin<br>insert @EmpleadosOrdenes values (@EmpleadoCodigo,@EmpleadoNombreCompleto, @EmpleadoCantidadOrdenes)<br>fetch next from cursorEmpleadosOrdenes<br>into @EmpleadoCodigo,@EmpleadoNombreCompleto, @EmpleadoCantidadOrdenes<br>end<br>close cursorEmpleadosOrdenes<br>deallocate cursorEmpleadosOrdenes<br>select * from @EmpleadosOrdenes order by EmpleadoCantidadOrdenes desc<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\/05\/ManualSQL_CursoresVariablesTipoTabla_01.png\" alt=\"\" class=\"wp-image-1632\" width=\"748\" height=\"357\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CursoresVariablesTipoTabla_01.png 740w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CursoresVariablesTipoTabla_01-300x144.png 300w\" sizes=\"auto, (max-width: 748px) 100vw, 748px\" \/><figcaption>Resultado del cursor de empleados.<\/figcaption><\/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 class=\"has-medium-font-size\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Crear un cursor que muestre un producto y la cantidad de unidades vendidas y el monto total generado.<\/strong><br>&#8212; Crear la variable tipo tabla<br>Declare @ProductosTotales table<br>(ProductoCodigo nchar(5), ProductoDescripcion nvarchar(50),<br>ProductoTotalUnidades Numeric(9,2), ProductosMontoTotal Numeric(9,2))<br>&#8212; Crear las variables para que el cursos guarde los datos<br>declare @ProductoCodigo nchar(5)<br>declare @ProductoDescripcion nvarchar(50)<br>declare @ProductoTotalUnidades Numeric(9,2)<br>declare @ProductosMontoTotal Numeric(9,2)<br>&#8212; Crear el cursor<br>declare CursorProductosTotales cursor for<br>select Right(&#8216;0000&#8217; + LTrim(Str(P.ProductID)),5) As &#8216;C\u00f3digo&#8217;,<br>P.ProductName As &#8216;Descripci\u00f3n&#8217;,<br>Sum(D.Quantity) As &#8216;Cantidad&#8217;,<br>Sum(D.Quantity * D.UnitPrice) As &#8216;Monto Total&#8217;<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>group by P.ProductID, P.ProductName<br>open CursorProductosTotales<br>fetch next from CursorProductosTotales into @ProductoCodigo, @ProductoDescripcion,<br>@ProductoTotalUnidades, @ProductosMontoTotal<br>&#8212; Recorrer el cursor<br>while @@fetch_status = 0<br>begin<br>insert @ProductosTotales values (@ProductoCodigo, @ProductoDescripcion,<br>@ProductoTotalUnidades, @ProductosMontoTotal)<br>fetch next from CursorProductosTotales into @ProductoCodigo, @ProductoDescripcion,<br>@ProductoTotalUnidades, @ProductosMontoTotal<br>end<br>close CursorProductosTotales<br>deallocate CursorProductosTotales<br>select * from @ProductosTotales order by ProductoDescripcion<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\/05\/ManualSQL_CursoresVariablesTipoTabla_02.png\" alt=\"\" class=\"wp-image-1633\" width=\"749\" height=\"458\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CursoresVariablesTipoTabla_02.png 917w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CursoresVariablesTipoTabla_02-300x184.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CursoresVariablesTipoTabla_02-768x471.png 768w\" sizes=\"auto, (max-width: 749px) 100vw, 749px\" \/><\/figure>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Cursor con variable tipo tabla en SQL Server Los cursores guardan en memoria el resultado de una instrucci\u00f3n Select para luego poder recorrer los registros y poder con cada uno de ellos realizar alg\u00fan tipo de trabajo.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=1630\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1631,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[232,8],"tags":[25,73,171],"class_list":["post-1630","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cursoressql","category-programacion","tag-cursores","tag-declare-cursor","tag-variable-tipo-tabla","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1630","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=1630"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1630\/revisions"}],"predecessor-version":[{"id":1634,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/1630\/revisions\/1634"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1631"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}