SQL Server 2005 Weird varchar Behavior

This SQL Server 2005 T-SQL code:

DECLARE @Test1 varchar;
SET @Test1 = 'dog';

DECLARE @Test2 varchar(10);
SET @Test2 = 'cat';

SELECT @Test1 AS Result1, @Test2 AS Result2;

produces:

Result1 = d Result2 = cat

I would expect either

1. The assignment SET @Test1 = 'dog'; to fail because there isn't enough room in @Test1
2. Or the SELECT to return 'dog' in the Result1 co开发者_JS百科lumn.

What is up with @Test1? Could someone please explain this behavior?

Let me answer with some quotes from the SQL Server documentation.

char and varchar

varchar[(n)]

...

When n is not specified in a data definition or variable declaration statement, the default length is 1.

Converting Character Data

When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated.

So, your varchar is declared as a varchar(1), and the implicit conversion in your SET statement (from a string literal of length 3 to a varchar(1)) truncates dog to d.

the varchar is defaulting to length one

DECLARE @Test1 varchar;

try this, which will uses a simple function that takes a sql_variant and returns the data type info back:

CREATE FUNCTION [dbo].[yourFunction]
(
     @InputStr      sql_variant   --can not be varchar(max) or nvarchar(max)
)
returns
varchar(8000)

BEGIN
    DECLARE @Value varchar(50)
    --can use SQL_VARIANT_PROPERTY(@InputStr,'BaseType') to determine given datatype

    --do whatever you want with @inputStr here
    IF @InputStr IS NULL
    BEGIN
        SET @value= 'was null'
    END
    ELSE IF SQL_VARIANT_PROPERTY(@InputStr,'BaseType')='varchar'
    BEGIN
        --your special code here
        SET @value= 'varchar('+CONVERT(varchar(10),SQL_VARIANT_PROPERTY(@InputStr,'MaxLength '))+') - '+CONVERT(varchar(8000),@InputStr)
    END
    ELSE IF SQL_VARIANT_PROPERTY(@InputStr,'BaseType')='datetime'
    BEGIN
        --your special code here
        SET @value= 'datetime - '+CONVERT(char(23),@InputStr,121)
    END
    ELSE IF SQL_VARIANT_PROPERTY(@InputStr,'BaseType')='nvarchar'
    BEGIN
        --your special code here
        SET @value= 'nvarchar('+CONVERT(varchar(10),CONVERT(int,SQL_VARIANT_PROPERTY(@InputStr,'MaxLength '))/2)+') - '+CONVERT(varchar(8000),@InputStr)
    END
    ELSE
    BEGIN
        --your special code here
        set @value= 'unknown!'
    END

    RETURN  @value

END
GO

DECLARE @Test1 varchar;
SET @Test1 = 'dog';

DECLARE @Test2 varchar(10);
SET @Test2 = 'cat';

SELECT @Test1 AS Result1, @Test2 AS Result2;

select [dbo].[yourFunction](@test1)

output:

Result1 Result2
------- ----------
d       cat

(1 row(s) affected)


-------------------
varchar(1) - d

(1 row(s) affected)

moral of the story, don't be lazy, specify a length on all of your varchar values!!!