Can I return a varchar(max) from a stored procedure?

VB.net web system with a SQL Server 2005 backend. I've got a stored procedure that returns a varchar, and we're finally getting values that won't fit in a varchar(8000).

I've changed the return parameter to a varchar(max), but how do I tell the OleDbParameter.Size Property to accept any amount of text?

As a concrete example, the VB code that got the return parameter from the stored procedure used to look like:

objOutParam1 = objCommand.Parameters.Add("@RStr", OleDbType.varchar)
objOutParam1.Size = 8000
objOutParam1.Direction = ParameterDirection.Output

What can I make .Size to work with a (max)?

Update:

To answer some questions:

For all intents and purposes, this text all needs to come out as one chunk. (Changing that would take more structural work than I want to do - or am authorized for, really.)

If I don't set a size, I get an error reading "String[6]: the Size property has an invalid size of 0."

.net sql-server-2005 stored-procedures

Edit Report

1 Answer

-1

The short answer is use TEXT instead of VARCHAR(max). 8K is the maximum size of a database page, where all your data columns should fit in except BLOB and TEXT. Meaning, your available capacity is less than 8k because of your other columns.

BLOB and TEXT is so Web 1.0. Bigger rows mean bigger database replication time, and bigger file I/O. I suggest you maintain a separate file server with an HTTP interface for that.

And, for the previous column

DataUrl VARCHAR(255) NOT NULL,

When inserting a new row, first compute the MD5 checksum of the data. Second, upload the data to the file server with the checksum as the filename. Third, INSERT INTO ...(...,DataUrl) VALUES(..., "http://fileserver/get?id=" . md5_checksum_data)

With this design, your database will stay calm even if the average data size becomes 1000x.

answered 2008-10-21T21:36:18.393

Can I return a varchar(max) from a stored procedure?

1 Answer

Your Answer