CODEWITHSUNDEEP
sqlstored-procedures
Osama Mortada
Osama Mortada

Reputation: 171

SQL stored procedure parameter without length

Can I use varchar parameters in my stored procedure without declaring its length? For example I would like to declare my stored procedure as follows:

CREATE PROCEDURE [TEST] 
    @Domain varchar,
    @Numbers int
AS
 .....

It's much easier for me if the stored procedure can automatically detect the parameter length, in this case if I changed the column length in my table I will not need to go and update all my stored procedures that uses this column.

Thank you,

Upvotes: 2

Views: 2410

Answers (2)

ig0774
ig0774

Reputation: 41267

I'm just guessing that you're using SQL Server here, in which case you could specify varchar(max) rather than specifying a specific length. As the documentation for SQL Server notes, an unspecified length is treated as length 1.

Other databases do not exhibit the same behavior. For example, PostgreSQL treats unspecified length as maximum length.

Note that the varchar(x) or (ANSI-standard) character varying(x) are pretty much compatible across databases.

Upvotes: 2

Andomar
Andomar

Reputation: 238086

If you omit the length, it defaults to one character.
For example: varchar is a synonym for varchar(1).

One way to define the length in one place is a type, like:

create type Domain from varchar(30) not null;

You can then use this new type in other definitions:

create procedure TestProcedure @par1 domain as select @par1
go
create table TestTable (col1 domain)

However, you can't change the definition of a type without dropping everything that uses it.

In my experience, data length changes are rare, and when they happen, they're easy to refactor manually. So I'd stick to varchar(x) instead of a type.

Upvotes: 5

Related Questions