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

by Andrew Piliser   Last Updated September 11, 2019 12:05 PM - source

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.



Answers 2


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;
Tulains Córdova
Tulains Córdova
July 20, 2016 18:18 PM

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
  ...
Michael Green
Michael Green
September 11, 2019 11:59 AM

Related Questions


Multiple stored procedures or 1 with parameter

Updated April 11, 2019 17:05 PM

Generating reports from large datasets

Updated July 14, 2016 08:02 AM