SQL Server 2005 Weird varchar Behavior
This SQL-SQL 2005 T-SQL code:
DECLARE @Test1 varchar;
SET @Test1 = 'dog';
DECLARE @Test2 varchar(10);
SET @Test2 = 'cat';
SELECT @Test1 AS Result1, @Test2 AS Result2;
gives:
Result1 = d Result2 = cat
I would expect that
- The assignment
SET @Test1 = 'dog';
fails because there is not enough space in@Test1
- Or
SELECT
, to return the "dog" in the "Result" column.
What's going on with @Test1
? Can anyone explain this behavior?
a source to share
Let me answer 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 large for the new data type are truncated.
So your varchar is declared as varchar(1)
, and the implicit conversion in your expression SET
(from a string literal of length 3 to varchar(1)
) truncates dog
to d
.
a source to share
varchar is 1 by default
DECLARE @Test1 varchar;
try this, which will use a simple function that takes sql_variant and returns datatype information 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)
exit:
Result1 Result2
------- ----------
d cat
(1 row(s) affected)
-------------------
varchar(1) - d
(1 row(s) affected)
moral of the story, don't be lazy, give a length for all your varchar values โโ!!!
a source to share