troubleshooting Question

SQL Length more than 8000 causes error

Avatar of vj_mi
vj_mi asked on
Microsoft SQL Server
5 Comments1 Solution640 ViewsLast Modified:

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?


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros