Varchar(8000) limit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Varchar(8000) limit

Author

Topic

mbrown906
Starting Member

5 Posts

Posted - 2005-03-28 : 12:42:04

Hello,

I have run into a problem. Let me give you some background to my situation and hopefully someone can help me out. I have a data entry form; on this HTML form I have many multiple select list boxes. I have been storing this data into a table. I am then made a stored procedure from this article http://www.sqlteam.com/item.asp?ItemID=2652

Everything works great except for the fact that I am limited by the data I can pass to that stored procedure. I can only pass varchar(8000). We running reports for a previous month I easily hit 20,000 char. How can I get around this limit?

I greatly appreciate your help!

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-03-28 : 12:52:59

TEXT fields
Be sure to read the BOL about how to work with TEXT fields. They are much different than VarChar Fields.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

mbrown906
Starting Member

5 Posts

Posted - 2005-03-28 : 14:50:00

I could use a little guidance. I have changed the data type to text. In my stored procedure I still max out at 8000 Char somewhere. Here is my stored procedure:

================================================================
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

ALTER PROC QA_coach_codes @csv varchar(8000)
AS

SELECT title, settings, catnamecall = (Select title from tblqacategories as qac
where tqac.subcatid = qac.subcatid and tqac.catid = qac.catid
AND qac.iscat = 1
and qac.active = 1 and qac.settings=(select id_score from tblqasettings where type ='c' and active = 1)),
catnameemail = (Select title from tblqacategories as qac
where tqac.subcatid = qac.subcatid and tqac.catid = qac.catid
AND qac.iscat = 1
and qac.active = 1 and qac.settings=(select id_score from tblqasettings where type ='e' and active = 1)),

replace(
SubString(@csv, ID_count ,
CharIndex(',' ,@csv, ID_count) - ID_count)
, ' ', '') AS coachcodeid, catid, subcatid
FROM tblCount, tblQACategories as tqac
WHERE
--ID_count <= Len(@csv)
--AND
SubString(@csv , ID_count - 1, 1) = ','
AND CharIndex(',' ,@csv, ID_count) - ID_count > 0
AND replace(
SubString(@csv, ID_count ,
CharIndex(',' ,@csv, ID_count) - ID_count)
, ' ', '') = tqac.id
order by catid, subcatid, iscat, id

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
================================================================

mbrown906
Starting Member

5 Posts

Posted - 2005-03-28 : 14:57:03

I had changed "@csv varchar(8000)" to "@csv text"
Sorry about the typo. Could someone please give some guidance?

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-28 : 16:08:08

Never mind that, what does the SHOW PLAN Say about the query.

What exactly are you trying to do?

Brett

8-)

mbrown906
Starting Member

5 Posts

Posted - 2005-03-28 : 16:27:19

Basically I am passing a LONG CSV string to the stored procedure. The values in the CSV string are unique keys to another table. So I want to grab the data from the second table where the ID matches the value in the CSV string.

amachanic
SQL Server MVP

169 Posts

Posted - 2005-03-28 : 18:11:49

Install the UDF from this article:

http://sqljunkies.com/WebLog/amachanic/articles/SplitString.aspx

Then:

SELECT *
FROM YourTable
WHERE YourTableID IN
(SELECT * FROM dbo.SplitString(@csv, ',')

---
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net

mbrown906
Starting Member

5 Posts

Posted - 2005-03-28 : 20:02:46

With some modifications it works perfectly! Thank you so much I have spent so much time looking for a solution like this!

=D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources