Systems Engineering and RDBMS

Back to the Basics: Specify parameter lengths

Posted by decipherinfosys on February 7, 2009

This is one of the issues that we have seen in junior programmers or at times even senior developers who have not done database programming before or database programmers who have done PL/SQL (Oracle) programming and are now doing it for SQL Server.  When you use these data-types: varchar(n), char(n), nvarchar(n), nchar(n), you should always specify the value for n.  Now, if you do not specify the value for it, it will not give you an error but you will not be getting your desired results.  Here is an example:

CREATE PROC dbo.TEST_PROC
(
@parm1        varchar
)
AS
BEGIN
SET NOCOUNT ON
select len(@parm1), @parm1;

END
GO

In this procedure, we did not specify the length for the input parameter.  We just gave it as varchar.  Since the length is not known, SQL Server will default it to a length of 1.  You can see that when you execute this procedure … here is the example:

EXEC dbo.TEST_PROC ‘A really long string’

And you will see that the length is returned as 1 and it will return only “A” i.e. the first character.  The same is true whether we have the variable in a T-SQL block or whether it is a local variable in the procedure.  So, make sure that you always specify the length for your parameters.  This is one of the common mistakes that we have seen on projects in which folks are converting from Oracle to SQL Server.  This is infact documented in BOL (and see the note about CAST() and CONVERT() functions as well):

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: