I'm writing a lot of stored procedures for a SQL Server database that take an id or code that is a
CHAR instead of an
INT. Most of these haven't changed size in a long time, but when a customer wants to increase the size we generally do. This wouldn't be a problem except for the fact that any stored procedure with a parameter typed
CHAR(4) would suddenly break.
I have three ideas for keeping the column sizes in one location, but I don't know if I like any of them. First is to simply use a type that is much larger than necessary, eg
VARCHAR(100). Are there any negative consequences to this other than lack of clarity in the code?
Second is to have the scripts that generate the procedures look up the column size to dynamically define their variable types. This would be better, but I'm not sure it would be possible without a lot of dynamic SQL, and we'd still need to rerun all of the scripts when something changed.
Finally, whenever a script updates a column's size, it could also go and update all of the stored procedures referencing it. I like this the most, but in a big organization with many programmers it would be easy for someone to forget or to miss a procedure.
I know that using
INT ids or an ORM would avoid the problem entirely, but unfortunately there's already a lot of code using the strings, and refactoring the database is not a business priority.