Today's Byte #6: Specify Size For Varchar Parameters

Reply

Rad Posted: 10-12-2009 4:32 AM

rated by 0 users

When specifying varchar or nvarchar parameters in stored procedures or functions, get into the habit if specifying the size. If you don't you will find yourself in a situation with difficult to find bugs, because if you don't specify a size, SQL Server assumes a default of 1.

For instance, running the following code returns '1' instead of '12345'

declare @test varchar
set @test ='12345'
select @test

SQL Server will silently truncate the data and continue execution, leading to very subtle bugs

Make it a practice to be explicit like so

declare @test varchar(50)
set @test ='12345'
select @test

Be sure to chose a sensible size for your uses.