Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Length more than 8000 causes error

Posted on 2007-07-26
5
Medium Priority
?
618 Views
Last Modified: 2008-01-09
Hi,

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?

regards,
MI
0
Comment
Question by:vj_mi
5 Comments
 
LVL 93

Expert Comment

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

Accepted Solution

by:
lahousden earned 2000 total points
ID: 19575580
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)

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19575583
>Declare @strSQL  As varchar(8000)  'max allowed by SQL I think
indeed.
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))
AS  
begin
     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, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos)
               SET @Pos = CHARINDEX(',', @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END    

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



0
 
LVL 4

Expert Comment

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

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19577434
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]
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

810 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