Trabajando con stored procedures en SQL Server

El procesamiento de datos es una de las actividades más arduas a la que nos enfrentamos al desarrollar sistemas transaccionales, especialmente cuando se afectan grandes cantidades de datos. Por medio de procedimientos almacenados, o stored procedures (SP), el sistema de base de datos es capaz de ejecutar un conjunto de instrucciones bien coordinadas entre sí que afectan la información con el fin de lograr un objetivo dentro del sistema.

A continuación comparto algunas técnicas que pueden ser útiles para crear stored procedures confiables con una gran cantidad de instrucciones y gestionar los grandes bloques de información que se ven afectados por estos. Vale la pena mencionar que aunque este artículo utiliza como referencia las capacidades disponibles en SQL Server, la teoría básica y la mayoría de la sintáxis es aplicable a otros manejadores de base de datos.

Catálogo de procedimientos
La primera recomendación es tener un catálogo de stored procedures que nos ayude a tener un listado de los procedimientos con los que contamos. En su forma mínima, esta tabla simplemente tendría Id y nombre del proceso, pero puede extenderse para incluir otra información relevante, tal como desarrollador o fecha de actualización.

Bitácora de operaciones
Para llevar un registro de qué información es afectada por un procedimiento, podemos crear una tabla de auditoria, en donde se escribirá el Id del Proceso que tuvo que ver con la entrada, eliminación o actualización del registro. Por ejemplo, supongamos que en el cuerpo de nuestro procedimiento para registro de comisiones contiene la instrucción:

INSERT INTO COMISION (IdEmpleado, Comision)
VALUES (@IdEmpleado, @Comision)

Inmediatamente después (y dentro de la misma transacción) podríamos colocar la siguiente instrucción, haciendo referencia a nuestro catalogo de procesos:

INSERT INTO BITACORA_OPERACION (IdProceso, DescProceso, FechaAlta)
VALUES (@IdProceso, @DescProceso, @Getdate)

En donde @IdProceso tiene el valor de 1, y @DescProceso contiene el texto ingresado por el usuario para justificar esta comisión. Esto es muy útil para el seguimiento a información e incluso puede ser un requerimiento de seguridad o cumplimiento de normas en un corporativo.

Evitar la ejecución duplicada
Imaginemos que ejecutamos nuestro procedimiento de calcular comisiones de vendedores y éste introduce la información en una tabla. ¿Qué sucedería si volviéramos a ejecutar el mismo proceso por error? Bueno, no es difícil de imaginar, tendríamos información duplicada. Para evitar esto, podemos apoyarnos en nuestra tabla de bitácora, consultando si nuestra operación ha sido ejecutada recientemente, antes de ejecutarla. A nivel de interfaz, podríamos mostrarle al usuario un mensaje más descriptivo.

Depuración de variables
En caso de no contar con herramientas con capacidades de depuración para stored procedures, se puede usar la instrucción Select para conocer los valores que van tomando las variables al ejecutar el proceso, por ejemplo:

SELECT @Variable AS Variable

Por otro lado, cuando hay una falla en la ejecución del procedimiento, es muy probable que el valor de la variable nos aparezca vacío, por lo que esta instrucción no es muy útil para depurar fallas. En ese caso, la recomendación sería basarnos en el mensaje de error que arroje el servidor de base de datos donde indica la línea de código donde se está dando el error, y entonces utilizar cerca de esa línea la instrucción PRINT para que nos de un valor que permita ejecutar esa instrucción fuera del procedimiento y poder observar lo que está sucediendo.

Por ejemplo si el mensaje de error indica que el Subquery asignado a una variable está regresando más de un valor, podemos hacer un print para conocer los valores que se están usando para armar el query, y detectar el problema.

PRINT @IdEmpleado
SET @VarIdComision =
(SELECT IdComision FROM Comision where IdEmpleado = @IdEmpleado)

Monitoreo de la información
No porque nuestros procedimientos no marquen errores significa que funcionan correctamente. Por ejemplo, si en un procedimiento tenemos la instrucción IF @Variable > 1 y el proceso nunca entra en el IF (cuando se supone que debió haber entrado), entonces hay algo mal. Una herramienta de análisis de código nos ayudaría a conocer nuestra cobertura de código, pero dado que éstas herramientas no son comunes para stored procedures, lo más seguro es que debamos recurrir a monitorear esto manualmente revisando nuestros datos y la bitácora de operaciones.

Bitácora de incidencias
Cuando ocurre algún error en nuestro proceso es bueno anotar en una bitácora por qué se dio el error y cómo se solucionó. En ocasiones, los errores se dan no porque esté mal nuestro proceso sino porque hay una inconsistencia en los datos. Por ejemplo si el proceso busca el Id de un registro con una fecha, pero existen dos registros con la misma fecha, SQL no sabrá cual tomar y el proceso fallará. Así que se toma la acción correctiva correspondiente y se anota en la bitácora para posibles futuras excepciones.

Separación en funciones
Cuando manejamos grandes cantidades de información, es normal que los procesos que desarrollemos tengan una gran cantidad de instrucciones dando como resultado código muy largo. El uso de funciones en SQL permite simplificar el código en ciertas secciones donde necesitemos obtener un valor. Las funciones las podremos invocar una y otra vez desde nuestros procedimientos.

DECLARE @SaldoActualCta Smallmoney
SET @SaldoActualCta = (SELECT dbo.fnc_ObtenerSaldoActualCuentaXIdCta(@IdCtaLote))

Manejo de cursores
Hay quienes argumentan que no es bueno el uso de cursores porque son supuestamente “lentos”, pero considero que hay ocasiones en que es inevitable, e inclusive muy útiles, ya que el uso de cursores nos permitirá avanzar registro por registro y especificar una o varias instrucciones en cada vuelta. Cuando se opte por usar cursores hay que tener cuidado de cerrarlos correctamente, principalmente cuando utilizamos cursores anidados (cursor dentro de otro cursor). Otra cosa a tomar en cuenta es que si se produce un error dentro del cursor, supongamos en un INSERT, cuando manejemos la excepción, también debemos cerrar el o los cursores involucrados. Por otro lado, para avanzar registro por registro no solamente se cuenta con la opción de cursores sino también con la instrucción WHILE, pero seria cuestión de revisar cual de estas opciones se adecua más a nuestras necesidades.

Tablas temporales
Esta es otra arma que tenemos a nuestra disposición al manejar grandes cantidades de información. Las tablas temporales permiten almacenar información temporalmente, de tal manera que podemos hacer uso de ella durante la ejecución de nuestro stored procedure. Recordemos también que existen las llamadas variables de tabla que son similares a las tablas temporales pero utilizan menos recursos y son ideales para almacenar poco volumen de datos. Se recomienda investigar a fondo las diferencias entre ambas opciones ya que su explicación excede el alcance de este artículo.

Disparadores y reglas
Los disparadores (también conocidos como triggers) son objetos de la base de datos que ejecutan acciones cuando se producen ciertos eventos. Por ejemplo imaginemos que a un vendedor inactivo no se le puede asignar una cuenta. Mediante un disparador podremos prevenir esta situación.

Si al ejecutar el procedimiento se da esta situación, entonces aparecerá un mensaje que impedirá que se termine de ejecutar el SP correctamente. Esto es bueno para nosotros, porque es una advertencia de que algo anda mal en nuestro procedimiento. Por otro lado, las reglas (rules) nos permiten establecer ciertos limites a la información. Por ejemplo, imaginemos que el saldo de un vendedor no puede ser menor a 1,000 pesos. Para esto podemos crear una regla.

El uso de disparadores y reglas en la construcción de procedimientos es fundamental. De hecho, se recomienda que antes de programar el procedimiento, se programen todos los disparadores y reglas necesarias. Esto nos obligará a programar el proceso respetando siempre las reglas de negocio preestablecidas. El uso de disparadores y reglas nos garantizara que la información que resulte de nuestros procesos será confiable.

Conclusión
Los stored procedures son fundamentales para el desempeño óptimo de un sistema de información. A los desarrolladores de sistemas de información nos corresponde implementar todo tipo de estrategias y técnicas que nos permitan tener el control de la información y garantizar que ésta sea confiable y precisa. Espero que los tips compartidos en este artículo los ayuden a lograr esta tarea.

Referencias
[1] Solid Quality Learning. Microsoft SQL Server 2005: Técnicas Aplicadas, Microsoft Press.