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:
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 free 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:
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.