Cómo modificar de forma segura procedimientos almacenados SQL y parámetros de funciones SQL

Modificar los parámetros de una función SQL o un procedimiento almacenado no es una tarea simple si deseamos mantener las dependencias y la integridad de la base de datos. ApexSQL Refactor es un complemento para SQL Server Management Studio y Visual Studio para formato SQL con una colección de refactorizaciones útiles de código. En este artículo, describiremos una refactorización particular para la modificación segura de parámetros.

La característica que le ayudará a modificar el procedimiento almacenado SQL y los parámetros de la función SQL mientras mantiene las dependencias es la característica Change parameters, y está localizada debajo del menú ApexSQL Refactor, debajo del sub menú de refactorizaciones:

La refactorización Change parameters añade, remueve o cambia un parámetro para una función o procedimiento SQL Server (desencadenadores incluidos) sin romper la integridad de la base de datos. El Script SQL generado por esta característica refleja el parámetro seleccionado desde la función/procedimiento, y actualiza todos los objetos dependientes de la base de datos.

Para remover, por ejemplo, el parámetro llamado @P2 usando la refactorización Change parameters, tenemos el siguiente script:

CREATE FUNCTION [DBO].[MYFUNCTION] (
@P1 INT = 0,
@P2 INT
)
RETURNS INT
BEGIN
RETURN 1
END
GO

Que cambiará a:

ALTER FUNCTION [DBO].[MYFUNCTION] (@P1 INT = 0)
RETURNS INT
BEGIN
 RETURN 1
END
GO

Tan simple como pueda parecer, el escenario de un caso real es diferente. Necesitamos ocuparnos de las referencias que pueden haber sido rotas removiendo el parámetro. Para asegurar la integridad de la base de datos, todas las referencias al procedimiento o función cambiados necesitan ser actualizadas también. Los argumentos extra en las llamadas no revisadas de funciones y procedimientos causan un error de conteo de argumentos, lo que significa que tienen referencias rotas. Usando la característica Change features, esto puede ser evitado.

Para usar esta característica, simplemente seleccione un objeto en Object Explorer, seleccione ApexSQL Refactor desde el menú de SQL Server Management Studio/Visual Studio y debajo de Other refactors, haga clic en la opción Change parameters. Alternativamente, presione Ctrl+Alt+Shift+Z. El siguiente diálogo aparece, ofreciendo múltiples opciones para modificar los parámetros del procedimiento almacenado SQL, ya sea que desea añadir, remover, actualizarlos o incluso cambiar su orden si es necesario:

Una vez que las modificaciones sean hechas, revise el impacto de los cambios resultantes contra su base de datos haciendo clic en la opción Preview. ApexSQL Refactor ofrece una vista previa detallada y un mecanismo de análisis previo al cambio real del parámetro, así que usted puede revisar lo siguiente:

La pestaña Signature provee un script CREATE para el procedimiento o función seleccionado que contiene los parámetros actualizados. Este script puede ser usado para crear el mismo procedimiento o una función en un ambiente separado o una base de datos diferente, si es necesario:

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo] 
 @BusinessEntityID INT = 0,
 @Title NVARCHAR(50) = N'N',
 @HireDate DATETIME = GetDate,
 @RateChangeDate DATETIME = GetDate,
 @Rate MONEY = 0,
 @PayFrequency TINYINT = 0,
 @CurrentFlag BIT = 0
AS

CREATE PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo] @BusinessEntityID INT = 0,
	@JobTitle NVARCHAR(50) = N'N',
	@HireDate DATETIME = GetDate,
	@RateChangeDate DATETIME = GetDate,
	@Rate MONEY = 0,
	@PayFrequency TINYINT = 0,
	@CurrentFlag dbo.Flag = 0
AS

La pestaña Generated script muestra un script SQL completo para ejecutar el cambio. Si usted revisa la opción Include summary description, la cabecera del script generado contendrá el sumario para el procedimiento de cambio de parámetros, por ejemplo:

-- Summary for the change parameters
-- Actions in Sequence:
-- Alter Procedure uspUpdateEmployeeHireInfo
-- Warnings:
-- Medium - The result script requires a user's analysis because the parameter type @CurrentFlag that has been changed, is used in uspUpdateEmployeeHireInfo procedure's body
-- Referencing Objects:
-- None

La pestaña Warnings muestra cualquier problema potencial, como cambiar el tipo de un procedimiento o función a uno que no puede ser aplicado, o cambiar un parámetro que ha sido usado en algún otro lugar en el cuerpo del procedimiento o función:

La pestaña Sequence muestra qué operaciones, y en qué orden deben ser hechas para cambiar los parámetros, usted puede simplemente expandir la lista y revisar las operaciones una por una:

La pestaña Dependences muestra una lista de objetos que necesitan ser alterados para trabajar correctamente con los parámetros cambiados. ApexSQL Refactor muestra todos los objetos influenciados por el cambio y automáticamente refactorizados para preservar la integridad de la base de datos:

Una vez que la modificación de procedimientos almacenados o parámetros de función SQL finaliza, junto con el análisis de impacto de modificación la opción Create script puede ser usada para dar una revisión final al script de modificación, o para aplicar todos los cambios necesarios sin miedo de que la integridad de la base de datos sea rota.

Modificar los parámetros de las funciones y los procedimientos almacenados SQL no necesariamente tiene que significar ir a través de las dependencias de la base de datos SQL para actualizar todas las dependencias manualmente, este complemento gratis para SQL Server Management Studio y Visual Studio puede hacer el trabajo automáticamente.

Recursos útiles:

Modificar un procedimiento almacenado SQL
Modificar una función SQL
Cómo modificar un procedimiento almacenado en SQL Server Management Studio