Post reply

Size limitation of variable in query analyzer

  • September 27, 2008 at 5:39 am

    #198656

    I am using sql server 2000 and have a stored procedure that builds a query based on parms from outside. I place the query in a variable called @strSQL varchar(8000).

    When I run this in query anayzer it doesn't run because not all of the query is placed into @strSQL. I believe the is a size limitation of 8000. Is this true and is there also the same limitation on stored procedures.

  • September 27, 2008 at 6:11 am

    #878068

    Stored procedures have no length limit. The max size of a variable on SQL 2000 is 8000 characters (varchar(8000).

    You can build the query into multiple variables and exec them together:

    EXEC(@sSQL1 + @sSQL2 + @sSQL3)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 27, 2008 at 7:37 am

    #878077

    Thanks. I will give that a try.

  • September 27, 2008 at 5:11 pm

    #878100

    Actually, there IS a limit on the size of stored procedures and batches... it's (65,536 * Network packet size) for batches and the lesser of batch size or 250 MB for stored procedures.

    Heh... they way I've seen some folks write code, it might not be enough. 😛

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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