Sql-server – Which problems arise, declaring the size of all varchar parameters as max in stored proc

sql-server-2005t-sqlvarchar

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time.

Increasing just the size of a column in a table is a rather simple task.

But when your shop adheres to the rule that the length of stored procedures parameters has to match the length of the corresponding table columns, an avalanche of changes through your stored procedures goes down hill.

Locking at Oracle shows, that procedures don't need to know about the length of varchar2 parameters.

What are the pros and cons, when you just declare each varchar parameter as varchar(max) or nvarchar(max)?

Obviously you can forget the maintenance nightmare of having to adjust your procedures each time you increase your column sizes.

Answer to SQLRockStar:
I want to keep a rather conservative table design, where fields, with a known limited length are modeled according to the current restrictions without reserve for possible future extensions.

Many of my tables contain an id int, a code varchar(30) and a description column varchar(255).

The code field has to reflect local naming conventions of different customers.

Some costumers need this field increased to 50.

When I do not code the length into the procedure parameters, I need only adjust the table column of this single costumer.

When I insist to code the length into the parameters, then I have either to support different versions of procedures for different customers, or I have to change and roll out the change to all customers.

Example

-- SQL Server
Create Table dbo.SQLServerTable (
    id int identity(1,1) primary key not null,
    code varchar(30) not null,
    description varchar(255) not null
);
go
Create Procedure WrapInsertSQLServer1 (
    @code varchar(30),
    @description varchar(255)
) as
INSERT INTO SQLServerTable values (@code, @description);
go      

Create Procedure WrapInsertSQLServer2 (
    @code varchar(100),
    @description varchar(1000)
) as
INSERT INTO SQLServerTable values (@code, @description);
go

Note: When you increase the length in SQLServerTable by some character, you have to adjust WrapInsertSQLServer1, but WrapInsertSQLServer2 doesn't need to be changed.

-- Oracle
Create Table OracleTable (
    id NUMBER(10) primary key not null,
    code varchar2(30) not null,
    description varchar2(255) not null
)
/
CREATE SEQUENCE S_OT START WITH 1
/

Create OR Replace Procedure WrapInsertOracle (
    p_code varchar2,
    p_description varchar2
) as
begin
INSERT INTO OracleTable values (S_OT.NEXTVAL, p_code, p_description);
end;
/

Here the parameters of a stored procedure never have a length.

If you say WrapInsertSQLServer2 isn't OK => you are saying Oracle is inherently wrong

Best Answer

Since your talking about the proc params:

CREATE PROCEDURE myProc
  @var1 varchar(30),
  @var2 varchar(max)
AS
...

If the parameters are defined as the correct length, SQL will throw an error when calling the procedure. If you always define as (max) then you'll have to manually handle passed parameters that are too long for the destination table column before any processing in the proc happens.

So in a nutshell, more work on your end since you'll have to add error handling to the proc you didn't need before.

Related Question