Importance Of Specifying Variable Length For Varchar in SQL Stored Procedures

23-05-2012

If you don’t specify a variable length for NVarchar, Varchar variables, the parser will assume the length of 1.

It will not complain. Instead it will just truncate whatever you pass to the variable and use the first character.

This mistake can cost you lots of time. Especially if you are working on a stored procedure with a complex SQL query.

You will think you made a mistake in your stored procedure and it could be hours when you actually realize this 🙂

…
…
-- DON’T USE THIS
@NephFilter varchar,
@ElsewhereFilter varchar
…
…
…
…
-- USE THIS
@NephFilter varchar(1),  -- SPECIFY THE LENGTH. EVEN IF IT’S 1 SO YOU WILL PRACTICE THIS
@ElsewhereFilter varchar(100)
…
…

Menol

This entry was posted in SQL Server and tagged , , , , . Bookmark the permalink.

2 Responses to Importance Of Specifying Variable Length For Varchar in SQL Stored Procedures

  1. Adam K says:

    Great blog, Keep up the quality work!

  2. tutu says:

    Fantastic post however I was wondering if you could write a litte more on this topic? I’d be very grateful if you could elaborate a little bit more. Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *