max length for local variable

Question

Post reply

max length for local variable

bagnew

Old Hand

Points: 370
More actions
June 3, 2005 at 12:26 pm

#69097

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

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Looks like somebody misunderstood the question...

looks like it's me .

noeld SSC Guru Points: 96590 More actions · Answer 4

varchar can accept up to 8000 chars

declare @data varchar(8000)

select @data = replicate('*',8000)

select len(@data)

and if you try:

declare @data varchar(8001) -- read the error message

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

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.

bagnew Old Hand Points: 370 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

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

noeld SSC Guru Points: 96590 More actions · Answer 8

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Ninja's_RGR'us

SSC Guru

Points: 294069

June 3, 2005 at 12:58 pm

#563003

That's why I'm using a "short" example.

bagnew Old Hand Points: 370 More actions · Answer 10

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

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]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Hey we all gotta to learn that someday... and last Friday was my day .

ron k SSCertifiable Points: 6103 More actions · Answer 13

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.

Remarks

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.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Thanx for the info .

Bottom line is always declare the size!!!!!