Sql server stored procedure uses varchar max as parameter

Question

I have a relatively simple site that uses php + mssql. I have various stored procedures that perform simple insert and delete. The problem I am facing is that many of the parameters that I pass to the stored procedure have the same length as the column into which I will insert the data.

For example, I have a stored procedure for registering a user. The stored procedure has the @USERNAME parameter with varchar(12) as the type. columnn USERNAME in table USER is of the same type with this length. In principle, this is not a problem. But I would prefer to freely change the length of the USERNAME column without also changing the length of the stored procedure parameter.

I can not use the table parameters, because the mssql php drivers do not support them.

The only solution I can imagine is to simply use varchar (max) for the parameters of the stored procedure, but is this considered bad practice?

Answer 1

Short answer: Yes.

What does it mean if someone passed a username whose length was 2147,483,647 characters !? Using varchar (max), you mean that everything will be fine.

I agree with the comment by @DanGuzman. Column columns must be sized appropriately in the first place, then parameters and variables must be appropriately. If you need to resize a table column, then you should propagate this change elsewhere - this is a best practice approach.

Sorting table columns correctly is not always easy - how long should the username be? 12 seems short, 2,147,483,647 is definitely too long - somewhere in the middle ?;) @JohnCappelletti also has a valid point - storage and handling are cheap compared to change costs, so be wrong on the big side when calibrating.

If varchar (max) is bad and you don't want to follow best practice, is there a midpoint? Maybe use a factor of 10? Something is sufficiently higher than the actual size, but not excessive.

Another thing you need to pay attention to, regardless of how you evaluate your parameters, is silent truncations or truncation errors at runtime - explicitly check the lengths.