max length for local variable

  • 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

     

     

     

  • 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

  • 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)

  • Looks like somebody misunderstood the question...

    looks like it's me .

  • 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

  • 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.

  • 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

     

  • 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

  • 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

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

  • 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

     

     

  • 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]

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

  • 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.


  • 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