Cursores en Store Procedure SQL Server

Usando Cursores en Store procedures

Los cursores en SQL Server permiten almacenar en memoria un conjunto de registros resultado de una instrucción Select, el objetivo principal del uso de los cursores es recorrer los registros del conjunto de resultados y realizar algún proceso con cada uno.

En este artículo se muestran ejercicios usando cursores que pueden dar al lector una idea para llenar o crear algún reporte necesario, además de como usarlos cuando la instrucción select del cursor es dinámica. Para esto se han incluído cursores en procedimientos almacenados.

Para más información ver:
Cursores en SQL Server
Usando Cursores en SQL Server
Cursores con variables tipo tabla
Cursores en SP para llenar tablas
Funciones definidas por el usuario
Procedimientos almacenados

Usando la base de datos Northwind
use Northwind
go

Ejercicio 1

En este ejercicio se muestran sólo dos productos por categoría, seleccionando los que tienen mayor Stock.
Declare cursorCategorias cursor for select CategoryID, CategoryName from Categories
Open cursorCategorias
Declare @CodigoCategoria int, @NombreCategoria nvarchar(15)
Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria
Declare @DosProductosPorCategoria table
( Codigo int, Descripcion nvarchar(40), Precio Numeric(9,2), Categoria nvarchar(15))
While (@@FETCH_STATUS = 0)
Begin
–Crear el cursor para leer dos productos de la categoría actual
Declare cursorProductosPorCategoria cursor for
select Top 2 ProductID, ProductName, UnitPrice
from Products As P where CategoryID = @CodigoCategoria order by P.UnitsInStock desc
Open cursorProductosPorCategoria
Declare @CodigoProducto int, @NombreProducto nvarchar(40),@Precio Decimal
Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio
While (@@FETCH_STATUS = 0)
Begin
insert into @DosProductosPorCategoria
values (@CodigoProducto , @NombreProducto ,@Precio,@NombreCategoria)
Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio
End
Close cursorProductosPorCategoria
Deallocate cursorProductosPorCategoria
Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria
End
close cursorCategorias
Deallocate cursorCategorias
select Categoria, Codigo, Descripcion, Precio from @DosProductosPorCategoria
go
El resultado se muestra en la siguiente imagen

Dos productos por categoría

Ejercicio 2

Mostrar n productos por categoria, el valor será un parámetro en un SP, este ejercicio va a mostrar los productos com mayor precio. Note que la instrucción select del cursor se debe construir y luego usar el procedimiento almacenado sp_executesql para llenar el cursor.
Create or alter procedure spNProductosMasCarosPorCategoria
(@Cantidad int)
As
Declare cursorCategorias cursor for select CategoryID, CategoryName from Categories
Open cursorCategorias
Declare @CodigoCategoria int, @NombreCategoria nvarchar(15)
Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria
Declare @DosProductosPorCategoria table
( Codigo int, Descripcion nvarchar(40), Precio Numeric(9,2), Categoria nvarchar(15))
While (@@FETCH_STATUS = 0)
Begin
Declare @InstruccionSelect nvarchar(500)
Set @InstruccionSelect =
‘Declare cursorProductosPorCategoria cursor for
select Top ‘ + Trim(Str(@Cantidad)) + ‘ ProductID, ProductName, UnitPrice
from Products As P where CategoryID = ‘ + Trim(Str(@CodigoCategoria)) + ‘ order by P.UnitPrice desc’
Execute sp_executesql @InstruccionSelect
Open cursorProductosPorCategoria
Declare @CodigoProducto int, @NombreProducto nvarchar(40),@Precio Decimal
Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio
While (@@FETCH_STATUS = 0)
Begin
insert into @DosProductosPorCategoria
values (@CodigoProducto , @NombreProducto ,@Precio,@NombreCategoria)
Fetch cursorProductosPorCategoria into @CodigoProducto , @NombreProducto ,@Precio
End
Close cursorProductosPorCategoria
Deallocate cursorProductosPorCategoria
Fetch cursorCategorias into @CodigoCategoria, @NombreCategoria
End
close cursorCategorias
Deallocate cursorCategorias
select * from @DosProductosPorCategoria
go

Ejecutando el SP con 4 productos por cada categoría.
Exec spNProductosMasCarosPorCategoria 4
go
El resultado se muestra en la siguiente imagen

Ejercicio 3

Crear un cursor que muestre las n órdenes con mayor valor de los clientes. Para el calculo del total de la orden crear una Función definida por el usuario.

Creando una FDU para calcular el total de una orden
Create or alter function dbo.fduCalculaTotalOrden(@NumeroOrden int)
Returns Numeric(9,2)
As
Begin
Declare @Total Numeric(9,2)
set @Total =
(
Select ROUND(Sum((D.Quantity * D.UnitPrice) * ( 1- D.Discount)),2)
from [Order Details] As D
where D.OrderID = @NumeroOrden
)
Return @Total
End
go
Para mostrar el reporte requerido para un cliente. Órdenes para un cliente y sus totales, por ejemplo el cliente con código ALFKI.
select
C.CustomerID As ‘Cód. Cliente’,
C.CompanyName As ‘Cliente’,
O.OrderID As ‘Nº Orden’, Format(O.OrderDate,’dd/MM/yyyy’) As ‘Fecha’,
dbo.fduCalculaTotalOrden(O.OrderID) As Total
from Orders As O
join Customers As C on O.CustomerID = C.CustomerID
where O.CustomerID = ‘ALFKI’
order by Total desc
go

Ahora el procedimiento almacenado que permite mostrar las N órdenes con mayor valor de los clientes
Create or alter procedure spNOrdenesMasValorPorCliente
(@Cantidad int)
As
Declare cursorClientes cursor for select CustomerID, CompanyName from Customers
Open cursorClientes
Declare @CodigoCliente nchar(5), @NombreCliente nvarchar(40)
Fetch cursorClientes into @CodigoCliente, @NombreCliente
Declare @OrdenesPorCliente table
( Codigo nchar(5), Cliente nvarchar(40), Orden int, Fecha Date, Total Numeric(9,2))
While (@@FETCH_STATUS = 0)
Begin
Declare @InstruccionSelect nvarchar(500)
Set @InstruccionSelect =
(
‘Declare cursorTopNOrdenesPorClienteMasValor cursor for
select Top ‘+ trim(STR(@Cantidad)) +
‘ O.OrderID, O.OrderDate,
dbo.fduCalculaTotalOrden(O.OrderID) As Total
from Orders As O
where O.CustomerID = ‘ + char(39) + trim(@CodigoCliente) + char(39) +’
order by Total desc’
)
Execute sp_executesql @InstruccionSelect
Open cursorTopNOrdenesPorClienteMasValor
Declare @OrdenNumero int, @FechaOrden Date, @ImporteTotal Numeric(9,2)
Fetch cursorTopNOrdenesPorClienteMasValor into @OrdenNumero , @FechaOrden ,@ImporteTotal
While (@@FETCH_STATUS = 0)
Begin
insert into @OrdenesPorCliente
values
(@CodigoCliente, @NombreCliente,
@OrdenNumero , @FechaOrden ,@ImporteTotal)
Fetch cursorTopNOrdenesPorClienteMasValor into @OrdenNumero , @FechaOrden ,@ImporteTotal
End
Close cursorTopNOrdenesPorClienteMasValor
Deallocate cursorTopNOrdenesPorClienteMasValor
Fetch cursorClientes into @CodigoCliente, @NombreCliente
End
close cursorClientes
Deallocate cursorClientes
select * from @OrdenesPorCliente
go

Ejecutar el procedimiento para mostrar cuatro órdenes con mayor valor.
Execute spNOrdenesMasValorPorCliente 4
go
El resultado se muestra en la siguiente imagen.

Procedimiento almacenado sp_executesql

Permite ejecutar una instrucción SQL la que puede reusarse o que hay sido creada dinámicamente. Esta instrucción SQL puede contener parámetros.

Ejercicio 4

En este ejercicio se crea una instrucción sencilla para listar los registros de la tabla Products.
use Northwind
go
Declare @Instruccion nvarchar(200)
Set @Instruccion = ‘Select * from Products’
Execute sp_executesql @Instruccion
go
El resultado se muestra en la siguiente imagen.

Productos listado con el uso de sp_executesql