How to safely modify SQL stored procedure and SQL function parameters

Modifying parameters of a SQL function or a stored procedure is not a simple task if dependencies need to be kept and to maintain a database integrity. ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in for SQL formatting with a collection of useful code refactors. In this article, one particular refactor for safe parameter modification will be described.

The feature that will help to modify a SQL stored procedure and SQL function parameters while maintaining the dependencies is the Change parameters feature and it is located under the ApexSQL Refactor menu, under the Other refactors sub-menu:

The Change parameters refactor ads removes or changes a parameter for a SQL Server function or procedure (triggers included) without breaking the database integrity. The SQL script generated by this feature reflects the selected parameter from the function/procedure, and updates all dependent database objects.

To remove, for example, the parameter named @P2 using the Change parameters refactor, the following script:

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

will change to:

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

As simple as this may look, the real case scenario is different. References that may have been broken by removing the parameter need to be fixed. In order to ensure the integrity of the database, all references to the changed procedure or function need to be updated as well. Extra arguments in unrevised function and procedure calls cause an argument count error, which means that they have broken references. Using the Change parameters feature, this can be avoided.

To use this feature, simply select an object in Object Explorer, select ApexSQL Refactor from the SQL Server Management Studio / Visual Studio menu and under the Other refactors sub-menu, click the Change parameters command. Alternatively, press Ctrl+Alt+Shift+Z. The following window appears, offering multiple options to modify SQL stored procedure parameters, to add, remove, update them or even change their order if necessary:

Once the modifications are made, check the impact of resulting changes against a database with a click on the Preview button. ApexSQL Refactor offers detailed preview and analysis mechanism prior to actual parameter change, so the following can be checked:

The Signature tab provides a CREATE script for the selected procedure or function containing updated parameters. This script can be used to create the same procedure or a function in a separate environment or a different database, if necessary:

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

The Generated script tab shows a complete SQL script to execute the change. If the Summary description option is checked, the header of the generated script will contain the summary for the change parameters procedure, for example:

-- 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

The Warnings tab displays any potential problems, like changing a procedure or function type that cannot be applied, or changing a parameter that has been used elsewhere in the procedure’s or function’s body:

The Sequence tab displays what operations, and in which order need to be done to change parameters. The list can be simply expanded and the operations can be checked one by one:

The Dependencies tab displays a list of objects that need to be altered to work correctly with the changed parameters. ApexSQL Refactor shows all objects influenced by the change and automatically refactored to preserve database integrity:

Once the modifying SQL stored procedure or function parameters is finished, along with the modification impact analysis, the Create script option can be used to give another final review of modification script, or to apply all the necessary changes, with no fear that a database integrity will be broken.

Modifying SQL functions parameters and stored procedures doesn’t necessarily have to mean tumbling through the SQL database dependencies to update all dependencies manually, this add in for SQL Server Management Studio and Visual Studio can do the job automatically.

Useful resources:

Modify SQL stored procedure
Modify SQL function
How to modify a stored procedure in SQL Server Management Studio