VARCHAR issue

Question

Post reply

VARCHAR issue

francis-628050

SSC Veteran

Points: 291
More actions
March 13, 2008 at 2:31 pm

#187425

I have declared a VARCHAR variable when it returns to the SP and exceeds the limit of 8000 characters, it switches to ntext. My question is how I could switch back to varchar? Or could I do a testing when it exceeds let say >=5000 then I "SET" this variable to something else like SET @VARIABLE1 = 'abc'. Guys, any ideas !? (SQL 2000)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

If it's for a report, why not just leave it as NText all the way through?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

meghna.bhargav SSCrazy Points: 2634 More actions · Answer 2

SQL Server stores rows (and indexes) in pages. Pages are 8K in size. There is some required header information (i.e. next block in chain, etc.), which drops the usable space for rows to 8060. So a single row cannot exceed 8060 bytes and rows cannot span multiple pages. You have two choices:

Split the table into two tables:

Table1

Id int (PK)

Text1 varchar(8000)

Table2

Id int (PK and FK to Table1)

Text2 varchar(8000)

Use the text data type:

The text data type stores the data in separate data pages which can span multiple pages. In the row, you simply store a pointer to the first data page. You can optimize the table so that the text type is stored inline with the row so long as it doesn't exceed a certain size.

thanks

francis-628050 SSC Veteran Points: 291 More actions · Answer 3

Thank you guys for the suggestion. Might be I explain like this.

users would choose by checking for either a single salesman or "Select all", when I receive from Reporting Services the variable it is like this '123' and I have to do the decoration to have ('123'), which I put to be part of the SQL "Select ........ where ... IN @variable1 (therefore it becomes 'where field1 IN ('123')'. From the above if the string received exceeds 8000 characters I got the error "....ntext to varchar ....". I am using SQL 2000, my plan is I test the input of the variable and test it, if it exceeds LEN(@variable1)>=5000 then re(SET) to be shorter and do something on the "select" with no filter (not using 'IN').

Hopefully it is clear. Thanks again.