SQL Length more than 8000 causes error
Posted on 2007-07-26
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?