June 3, 2005 at 12:26 pm
Would someone mind confirming (or otherwise) that a local var: @myvar varchar(x) has a maximum length...less than the 8000 chars that a varchar field type can be? ...perhaps it is limited to 255?
Thanks in advance
B
June 3, 2005 at 12:32 pm
In SQL Server 2000 it is 8000 and in SQL Server 6.5 it is 255. I am not sure about 7.0
If it is not specified it would take the length as 1 (Varchar(1))
Regards,
gova
June 3, 2005 at 12:33 pm
Declare @m as varchar
select datalength(@m)
--null
set @m=''
Select top 1000 @m = @m + '12345678' from dbo.SysColumns
Select datalength(@m), @m
--1, 1
set @m = '1234'
select @m
--1
Any variable is null right after its declaration. And it appears that the default size for varchar and char is 1.
It's considered a best pratice to always specify the size of the variable (chars, decimal and binary)
June 3, 2005 at 12:38 pm
Looks like somebody misunderstood the question...
looks like it's me .
June 3, 2005 at 12:39 pm
Hey govinn... I ran the script and posted the results. I don't know about any other version than 2000 and I didn't check the books online.
June 3, 2005 at 12:39 pm
Perhaps to clarify:
I declare @myvar varchar(600)...then loop through a cursor building out a string of ids if criteria are met:
SET @myvar=@myvar+' '+@userid
So that I end up with a string of user id's...the problem is that the string builds to 255 chars then no further additions are added even though I am expecting more.
Thanks again B
June 3, 2005 at 12:40 pm
You don't need a cursor to do this, check this out :
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
June 3, 2005 at 12:50 pm
if by any chance you are checking that on QA, can you make sure that on Tools->Options->Results tab you have the "maximum column length" set to 8000
* Noel
June 3, 2005 at 12:58 pm
That's why I'm using a "short" example.
June 3, 2005 at 2:36 pm
Thanks for your help everyone....
Yes I had QA set to 255...which was throwing me off the mark...cauing me to look at the procedure... but since I was exporting the data (single column) to a text file with DTS and had column set to 'delimited' instead of 'fixed width'...which limited the output to 255 chars...same as QA...
Thanks again for the help
June 6, 2005 at 2:00 am
And it appears that the default size for varchar and char is 1
It does not only appear so, it is so
VARCHAR is equivalent to VARCHAR(1)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 6, 2005 at 8:34 am
Hey we all gotta to learn that someday... and last Friday was my day .
June 6, 2005 at 8:47 am
There's always an exception
char and varchar
Fixed-length (char) or variable-length (varchar) character data types.
char[(n)]
Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.
varchar[(n)]
Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.
June 6, 2005 at 8:51 am
Thanx for the info .
Bottom line is always declare the size!!!!!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply