Changing SQL stored procedures and functions parameters without breaking dependencies

Changing the parameters of a SQL function or a stored procedure is easy – simply ALTER the function or the procedure and add or remove parameters, change the names or data types of the existing parameters and even set default parameter values. However, changing a parameter safely, in most cases, is anything but easy

Namely, to ensure the integrity of SQL database and applications, all calls to the changed procedure or function need to be updated as well. Extra arguments in unrevised function and procedure calls cause argument count errors. Calls that don’t explicitly name arguments can pass arguments in the wrong order, causing data type errors or improperly executed functions or procedures. Additionally, functions or procedures that are executed infrequently can go unnoticed, throwing errors down the line that can often be hard to resolve.

The first step in ensuring that the parameter change won’t affect the functionality of the database is to identify all of the function’s or procedure’s dependencies. One of the ways to do so is using the SQL Server Management Studio. To do so:

1. In the Object Explorer navigate to the stored procedure or function the parameters of which want to change
2. In the context menu, click the View Dependencies command
3. In the Object Dependencies window, review both the objects the procedure or function depend on as well as the object which are dependent on them

Reviewing these objects and their respective dependencies is crucial in ensuring that the parameter change will go smooth, since for instance, SQL Server will allow to change the data type of an int parameter to char in a procedure which, when executed, updates an int column without any warnings. However, executing such a stored procedure will result in the column not being updated, and in some scenarios, unless the procedure’s return value is captured and processed, SQL Server itself will provide no indication that there has been a problem. To add insult to injury, there is no guarantee that the View Dependencies feature will display all the dependencies as SQL Server’s own dependency tracking system is often incomplete, inaccurate, or both. Having said all that, is there a safe way to change stored procedure’s or function’s parameters?

Fortunately, yes. This is where ApexSQL Refactor comes into play.

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in which formats and refactors SQL code using 11 code refactors and over 200 formatting options. It expands wildcards, fully qualifies object names, renames SQL database objects and parameters without breaking dependencies and much more

To change the parameters of the functions or stored procedures safely using ApexSQL Refactor:

1. Select the stored procedure in either SQL Server Management Studio or Visual Studio
2. In the ApexSQL menu, click ApexSQL Refactor
3. From the Other refactors sub-menu select the Change parameters command:
   

4. In the window that will appear add, remove, rename or reorder parameters, change their data type or set their default values
   

5. Click the Preview button to see how the parameter change will effect on a stored procedure or function
6. Select the Dependencies tab to preview the dependent objects which will be updated automatically to reflect the change
7. Click the Create script button to open script in the Query editor
8. Click the Execute button to apply the parameter changes

Changing the parameters of SQL functions and stored procedures doesn’t have to boil down to digging through dependencies and updating each one manually. With ApexSQL Refactor parameters can be changed, without risking to break the database, in just one click.