varchar size overflow
-
Hi,
I am using SQL SERVER 2000 + VB 6.0 with ADO to access data.
Inside a store procedure I am using dynamic SQL. My query is as given below:
DECALRE @strQuery varchar(8000)
SET @strQuery = 'Select * from tablename where columnname in + ' search_clause'
EXEC (@strQuery)
But this search_clause sometimes goes beyond the 8000 characters. And as such goes @strQuery also.
Right now to overcome this problem we have broken up the search_clause variable into 5 variables each are varchar(8000) and still this can overflow under extreme scenarios.
Below is the code snippet that we have used to overcome the problem.
EXEC (@strQuery1 + ' AND ' + @column_name + 'IN (' +
@search_clause_1 +
@search_clause_2 +
@search_clause_3 +
@search_clause_4 +
@search_clause_5 + ')'
 
Please suggest a permanant solution or a workaround to overcome this problem.
-
I saw you are using IN expression so I assume the @search_clause_?? are just values for the column @column_name.
You can create a temp table #ColValues and save all searching values into the table. Change your query to use inner join something like:
+ ' INNER JOIN #ColValues ColVal ON SourceTable.columnName=ColVal.ColumnName'
-
Hi,
Thankz for your reply. Same we have also thought of but where to create the # table.
If in SP I have to create then any how the problem remains in place. The search values anyhow I have to pass in SP by breaking in into number of paramater.
For example, If the search Values is above 8000 character then I need to have 2 parameter, if it is above 16000 then 3 parameter and so.
Also I can't create #table in VB code and populate the value over there as Scope of the #table remains till there. I can't get the #table in SP.
Please help
-
Pass your list of values to a table function that splits the values into separate records.
EG:
SELECT * FROM tablename tbl INNER JOIN [table_function_name](@search_clause, ',') as Itm ON tbl.columnname = Itm.Items
This is the function we use,
CREATE FUNCTION dbo.udf_SplitChar ( @vcrArray varchar(8000) , @chrDelim char(1) ) RETURNS @Results TABLE ( Items varchar(8000) ) AS
BEGIN
INSERT INTO @Results (Items) SELECT SUBSTRING(@vcrArray, n, CHARINDEX(@chrDelim, @vcrArray + @chrDelim, n) - n) AS [val] FROM dbo.Nums WHERE n <= LEN(@vcrArray) AND SUBSTRING(@chrDelim + @vcrArray, n, 1) = @chrDelim ORDER BY n - LEN(REPLACE(LEFT(@vcrArray, n), @chrDelim, '')) + 1RETURN END GO
it uses a general purpose numbers table which is just created like this.
CREATE TABLE [dbo].[Nums] ( [n] [int] NOT NULL , CONSTRAINT [PK_Nums] PRIMARY KEY CLUSTERED ( [n] ) ) END GO
DECLARE @i int
SET @i = 1
WHILE @i < 10000 BEGIN INSERT INTO [dbo].[Nums] VALUES ( @i ) SET @i = @i + 1 END GO
--------------------
Colt 45 - the original point and click interface
-
Hi,
Thankz for your reply. It really added some idea in reaching to a solution. But still actual problem is not addressed.
Let me explain you with example:
At fornt end I have one String variable say "ACstr". which have value upto 20,000 character.
Where ACstr = 'ABC, DEF, MNO, PQR,.........., XYZ'
But I cannot pass this string directly to Store procedure as @search_clause parameter which is of varchar(8000) size. This was what I was facing the problem initial. i.e. ACstr exceeding 8000 character.
So to get temporary solution what I did is I took four more string in Front end say strA, strB, strC and strD and passed 7000 character to each string from ACstr.
In SP say getInfo I removed @search_clause and added four parameter say @search_clause1, @search_clause2, @search_clause3 and @search_clause4.
SO now I call SP as EXEC getInfo strA, strB, strC, strD
But by this I am restricting the user upto the 32000 character which I don't want to do. Also I don't want keeping adding string variable and parameter into VB code and SP respectively.
Hope you understood the problem that I will face in future....
Please help
-
Well if you're going to have that many values, using them is the IN clause of a WHERE is the wrong way to approach it. You'll have a large performance hit depending on how long your IN clause is.
What I'd suggest is that your VB code inserts the values into a permanent worktable, not a # temp table. You can add a guid to allow for multiple users. Then your stored procedure just uses that worktable and you can have as many values as you could possibly want.
EG:
1) generate guid
2) user selects value(s)
3) as a batch, or while selections are being made, app inserts selections into worktable with guid
4) user indicates that they've finished selecting values
5) app executes getInfo procedure passing in single guid
6) getInfo procedure uses guid to join to worktable and match on users selections
7) at end of procedure, or when indicated by user, selections are cleared from worktable using the same guid
--------------------
Colt 45 - the original point and click interface -
You do not need to create the temp table in VB. It should be in your main SP your VB code is calling.
In your VB code, you need to compose an xml document that contains all your values and pass the xml as a NTEXT/TEXT parameter to the SP. In the SP you can use sp_xml_preparedocument, OPENXML, ... feature to parse and get individual column values and insert them into the temp table.
In your dynamic SQL, join with the temp table as I said in the previous post.
-
You could try this, but probably not the best solution (make sure the input ends with a comma !!!)
CREATE PROC usp_GetColumnName @ptr varbinary(16), @start int, @length int
as
READTEXT #temp.search_clause @ptr @start @length
GO
CREATE PROC usp_GetRows @search_clause text
AS
CREATE TABLE #temp (search_clause text)
INSERT INTO #temp (search_clause) VALUES (@search_clause)
CREATE TABLE #temp2 (columnname varchar(30))
DECLARE @ptr varbinary(16), @idx1 int, @idx2 int, @start int, @length int, @strQuery varchar(200)
SELECT @ptr = TEXTPTR(search_clause) FROM #temp
SET @idx1 = 1
SELECT @idx2 = CHARINDEX(',',search_clause,@idx1) FROM #temp
WHILE (@idx2 > 0)
BEGIN
SET @start = @idx1-1
SET @length = @idx2-@idx1
INSERT INTO #temp2 (columnname)
EXEC usp_GetColumnName @ptr, @start, @length
SET @idx1 = @idx2+1
SELECT @idx2 = CHARINDEX(',',search_clause,@idx1) FROM #temp
END
SELECT * FROM tablename a INNER JOIN #temp2 b ON b.columnname = a.columnname
DROP TABLE #temp
DROP TABLE #temp2
GO
EXEC usp_GetRows 'columnname1,columnname2,columnname3,'
Far away is close at hand in the images of elsewhere.
Anon. -
Use a text parameter instead - although these are more difficult to parse. You'll need to parse it into a separate temp table (a table variable would do) and then join to it as suggested by others...
Oops!! Just saw that David's solution pretty much does this! Sorry - use this as a high-level overview of his code
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply