January 31, 2005 at 1:47 pm
Hi,
We all know that varchar has max size limit of 8000 bytes. I have trouble executing an adhoc query via stored proedure in cases when the WHERE clause is greater than 8000 bytes.
I have a stored procedure that takes Query criteria in paramter @SQLCriteria of type TEXT. I then break the criteria into five pieces using the following code
DECLARE @SQLCriteriaPart1 varchar(8000) ,@SQLCriteriaPart2 varchar(8000) ,@SQLCriteriaPart3 varchar(8000) ,@SQLCriteriaPart4 varchar(8000) ,@SQLCriteriaPart5 varchar(8000)
SET @SQLCriteriaPart1 = substring(@SQLCriteria, (8000* 0) + 1, (8000* 1))
SET @SQLCriteriaPart2 = substring(@SQLCriteria, (8000* 1) + 1, (8000* 2)) + ''
SET @SQLCriteriaPart3 = substring(@SQLCriteria, (8000* 2) + 1, (8000* 3)) + ''
SET @SQLCriteriaPart4 = substring(@SQLCriteria, (8000* 3) + 1, (8000* 4)) + ''
SET @SQLCriteriaPart5 = substring(@SQLCriteria, (8000* 4) + 1, (8000* 5)) + ''
later on, I execute the dynamic sql using
EXEC ( @SQL + ' INTO ' + @TableName + ' FROM main_view WHERE (' + @SQLCriteriaPart1 + @SQLCriteriaPart2 + @SQLCriteriaPart3 + @SQLCriteriaPart4 + @SQLCriteriaPart5 + ')' + @SQLOrderBY )
the problem is that if @SQLCriteria exceeds 8k byte limit, the query gets chopped off and doesnt execute properly. I tried using sp_executesql like this,
EXEC sp_executesql @SQL + ' INTO ' + @TableName + ' FROM main_view WHERE (' + @SQLCriteriaPart1 + @SQLCriteriaPart2 + @SQLCriteriaPart3 + @SQLCriteriaPart4 + @SQLCriteriaPart5 + ')' + @SQLOrderBY , '', 1
but the above gives me syntax errors.
The criteria is actually running on two to three columns. Its just lots of terms ORed together. What happens is that the user is provided a set of lookup values to choose from for a particular column. And sometimes they select ALL lookup values, which makes my query WHERE clause size more than 8K.
There got to be some solution to this problem. My client wont allow me to move to Yukon. I have to use stored procedure, and i cannot use parameters.
Please help me on this.
January 31, 2005 at 2:07 pm
2 alternatives:
When they select 'ALL', does that essentially match every row based on a given column ? If yes, then selecting 'ALL' means you don't need any criteria on that column. There is no point in SQL like "Or SomeColumn = 'Value1' Or SomeCOlumn = 'Value2 .... 'ValueN' " if 'Value1' thru 'ValueN' encompasses the entire domain of values in the column.
You could also use a join to a temp table of table variable, instead of a huge list of OR'ed values. Insert the user selected criteria into a temp table and use IN
Where SomeColumn In (SelectQueryValue From #UserCriteria)
February 1, 2005 at 4:52 am
The approch i take in such situations is to pass a specific string from GUI to SP, then look for that specific string in SP and if found, just dont include the Criteria in where clause.
Hope it helps...
Naveen
February 1, 2005 at 8:30 am
I just read a long and detailed description of how to solve similar problems with query criteria on http://www.sommarskog.se/index.html - I'm almost sure you will find your answer there - articles "Dynamic Search Conditions in T-SQL" and "Arrays and Lists in SQL Server".
HTH, Vladan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply