How should I handle Stored Procedure parameters that need to be the same length as a column?

Question

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.

• Justin Cave: What database are you using? In Oracle, the answer is to use anchored data types (table.column%type). But that is hardly universal across databases.
• Andrew: We're using SQL Server
• Ewan: Varchar(50) all the way

Answer 1

You must declare the variable as being of the type of a table's column. That way if the table column changes the stores procedure needs not change.

v_myvar owner.table.column%type;

• Andrew: Is this limited to certain DBMSs? It doesn't seem to be working for me in SQL Server.
• Tulains Córdova: @AndrewPiliser it's Oracle PL/SQL.

Answer 2

One drawback of using overly long columns is in run-time memory allocation. SQL Server will use half the declared length when calculating how much memory to request. So for varchar(100) it will be 50 bytes per value.

This may not be a problem if the server has sufficient RAM or such queries are infrequent. If, however, it is every row in every table in every query the overhead could mount up. There is a risk that operations which may otherwise be entirely in memory start spilling to disk, with obvious performance impact.

Variable-length columns carry around with then an additional hidden internal field holding the length of the actual data. Again this is small by itself but could contribute to memory pressure depending on workload and configuration.

T-SQL has no way of tying a variable's type to a column's meta data. An approach could be to declare an alias type and use it rigorously throughout your code. Changes would then be limited to the column(s) and the alias.

CREATE TYPE my_customer_id FROM char(4);

and

CREATE OR ALTER PROCEDURE This_Proc
 @Some_Id AS my_customer_id
AS
 ...

The problem is that a user defined type cannot be ALTERed. To increase the length you'll have to define a new type (my_customer_id_6 perhaps?) and replace all the existing references. Some kind people have published scripts to automate this. The benefit is that these references will be easily found.