Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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."

like image 249
Electrons_Ahoy Avatar asked Oct 21 '08 17:10

Electrons_Ahoy


People also ask

Can we return value from stored procedure?

A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.

Why you shouldn't use VARCHAR Max?

You cannot create regular index on a VARCHAR(MAX) column. So, if you want a indexed string column then you have to go for VARCHAR(N). There is no direct way to restrict the length of the string stored in a VARCHAR(MAX) column. On the other hand, using VARCHAR(1-8,000), you can limit the string saved in the column.

How many values can be returned from a stored procedure?

A stored function can return only one value, unlike a stored procedure, which can return multiple values or an entire result set.

How do you DECLARE VARCHAR Max?

varchar [ ( n | max ) ] Use n to define the string size in bytes and can be a value from 1 through 8,000, or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB).


2 Answers

Upvoted Ed Altofer. (He answered first, so if you like my answer vote his too).

OleDb is your problem. It's a generic database connection that needs to talk to more than just SQL Server, and as a result you have a lowest common denominator situation where only the weakest composite feature set can be fully supported. One of the lost features is varchar(max) support.

You're using SQL Server 2005 and VB.Net. What's stopping your from using System.Data.SqlClient rather than System.Data.OleDb?

Edit
I found the documentation on the issue. See here:
http://msdn.microsoft.com/en-us/library/ms131035.aspx

The relevant portion:

Return values of data type varchar(max), nvarchar(max), varbinary(max), xml, udt, or other large object types can not be returned to client versions earlier than SQL Server 2005. If you wish to use these types as return values, you must use SQL Server Native Client.

like image 124
Joel Coehoorn Avatar answered Nov 09 '22 21:11

Joel Coehoorn


Can you use ADO.NET?

Edit: To clarify, I am just suggesting that you might want to consider ADO.NET since you're working with VB.NET 2005 and SQL Server 2005--OLEDB was the pre-.NET way of accessing databases, so you may find more flexibility by using ADO.NET instead.

You shouldn't return VARCHARs from a stored procedure. I'm not even sure you can.

However, if you use an OUT parameter, you shouldn't have to specify it by size. For example:

SqlParameter p = new SqlParameter("@RStr", SqlDbType.VarChar);
p.Direction = ParameterDirection.Output;

Not sure whether this will suit your needs, but it should work just fine.

like image 36
Ed Altorfer Avatar answered Nov 09 '22 20:11

Ed Altorfer