Post reply

VARCHAR (8000) VARIABLE IN CURSOR SETTING

  • August 25, 2005 at 4:19 pm

    #89584

    We have a developer who is running a cursor and storing the results into a varchar(8000) variable, adding each "find" into the variable as the cursor proceeds:  e.g.  FETCH NEXT FROM C INTO @v-2

                          SET @LARGEV = @LARGEV + ',' + @v-2

    My question is:  Does the operating system have to reallocate memory for

    @LARGEV each time @v-2 is added to it?  Would the @LARGEV variable be better declared as CHAR(8000)?

  • August 25, 2005 at 4:41 pm

    #585225

    Why does he do that?

  • August 25, 2005 at 5:23 pm

    #585232

    That's what he knows.

  • August 25, 2005 at 11:47 pm

    #585283

    If @largev would be declared as char(8000) it would always contain the value it was initially set to (probably with a lot of spaces padded to the right).

    declare @a char(8000)

    set @a = 'a'

    print @a

    set @a = @a + ',' + 'b'

    print @a

    Instead of using a cursor your developer should be writing something similar to this:

    DECLARE @largev VARCHAR(8000)

    SET @largev = ''

    SELECT @largev = @largev + someColumn + ',' FROM someTable

    Then just remove the trailing comma (if necessary) and he's set to go.

    --
    Chris Hedgate http://www.hedgate.net/
    Contributor to the Best of SQL Server Central volumes
    Articles: http://www.sqlservercentral.com/columnists/chedgate/

  • August 26, 2005 at 6:54 am

    #585354

    Exactly where I was going at .

Viewing 5 posts - 1 through 4 (of 4 total)

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