SQL Length more than 8000 causes error

Posted on 2007-07-26
Last Modified: 2008-01-09

I display all transactions in the grid in VB.NET. User can select any number of transactions. I then create a comma-separated list of the selected transactions and send the list to SQL Stored Procedure. This SP has a SQL and I concatenate the received transaction list as:
Declare @strSQL  As varchar(8000)  'max allowed by SQL I think
SET @strSQL = 'Update xxxxxxxxxxxxxxxxxxxxxxxxxxxx Inner Join xxxxxxxxxxxx'

SET @strSQL = @strSQL + ' Where Transaction Nr in (' + @TrnList + ')'

Now the problem is if the user selects large number of transaction, the the length of @strSQL goes beyond 8000 chars and my SP triiggers an error. Is there any way how I can define my @strSQL varibale to hold more than 8000 chars?

Question by:vj_mi
    LVL 92

    Expert Comment

    by:Patrick Matthews
    varchar maxes out at 8000.  Try breaking it up into two strings and concatenating in the EXEC.
    LVL 10

    Accepted Solution

    You are passing the string to EXEC, right?  E.g.

    Exec (@strSQL)

    If so, then you will not be able to build the string to contain more than 8000 characters.

    However, EXEC allows you to do more than 8000 characters if you split the text into chunks that are each less than 8000 characters and then you concatenate them in the EXEC call:

    E.g. you could break up @TrnList and do something like this:

    declare chunk1 nvarchar (8000)
    declare chunk2 nvarchar (8000)
    declare chunk3 nvarchar (8000)
    declare chunk4 nvarchar (8000)

    SET @chunk1 = 'Update xxxxxxxxxxxxxxxxxxxxxxxxxxxx Inner Join xxxxxxxxxxxx'
    SET @chunk2 =  ' Where Transaction Nr in (' + @TrnList1
    SET @chunk3 =   @TrnList2
    SET @chunk4 =   @TrnList3 + ')'

    EXEC (@chunk1 + @chunk2 +@chunk3 + @chunk4)

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    >Declare @strSQL  As varchar(8000)  'max allowed by SQL I think
    now, if @trnlist is that big, you might consider other solutions...
    especially if that IN() statement is the "only" reason for dynamic sql, use this:

    CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(500))
    returns @result TABLE (Value varchar(30))
         DECLARE @TempList table
              Value varchar(30)

         DECLARE @Value varchar(30), @Pos int

         SET @Parameters = LTRIM(RTRIM(@Parameters))+ ','
         SET @Pos = CHARINDEX(',', @Parameters, 1)

         IF REPLACE(@Parameters, ',', '') <> ''
              WHILE @Pos > 0
                   SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
                   IF @Value <> ''
                        INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
                   SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
                   SET @Pos = CHARINDEX(',', @Parameters, 1)

         INSERT @result
         SELECT value
            FROM @TempList

    and your code goes like this:
    WHERE Transaction_Nr in ( select value from dbo.ParmsToList(@TrnList))

    LVL 4

    Expert Comment

    I'd suggest putting values from @TrnList  into a temp table
    and then instead of
    SET @strSQL = @strSQL + ' Where Transaction Nr in (' + @TrnList + ')'
    SET @strSQL = @strSQL + ' Where Transaction Nr in (SELECT TrNo FROM #temp)'

    LVL 68

    Expert Comment

    You should definitely load the @TrnList values into a temp table.  You shouldn't even need dynamic SQL then:

    Update xxxxxxxxxxxxxxxxxxxxxxxxxxxx
    Inner Join xxxxxxxxxxxx
    Inner Join #TrnList tl ON tl.trn = [Transaction Nr]

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now