SQL Server Performance Forum – Threads Archive

executing adhoc query exceeding 8K bytes text size

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. Please help me on this. Before you ask more questions, think about whether you really want to know the answers

---

Assuming that you’re not searching on a very long string value, then you must be adding criteria for a lot of columns. The basic suggestion would be to break up these criteria into separate parameters for each column – but I know that’s not the answer you were looking for. You might get away by letting the client application prepare the whole big query statement by itself, instead of calling an sp – with this kind of criteria I doubt if SQL Server can do much about optimization anyway. Then it would depend on whether your data access library has any limitations about the length of query statements.

---

Unfortunately i have to do it within stored procedure itself as this guarantees the fastest execution time. 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.

Before you ask more questions, think about whether you really want to know the answers

---

How about using <pre id="code"><font face="courier" size="2" id="code">in (&lt;lookup value list&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’‘ /></font id="code"></pre id="code"> instead of multiple ORs?

---

Isn’t it possible to move the lookup-values to another (parameter) table so you only have to lookup some ids in stead of long descriptions ?

---

Like alzdba pointed out, there’s only one solution: upload the selected values to a working table, and join that working table in your query. Also allow users to "select all values", which is basically not selecting any one of them, and not join on the working table. Finally, a stored procedure must be well-designed and supported by proper server and database maintenance before you can assume the fastest possible execution time can be attained. The use of a stored procedure in itself does not guarantee anything.

---

]]>