Monday, March 17, 2008

Handling long strings with CLR SQL Stored Procedures

I've been banging my head on this one for a while now, though only now did I finally sit down to figure out to solve it.  I have (inherited from another dev) a CRL SQL Stored Procedure which outputs a long string.  The signature looks like:

public static void DoSomething(SqlString input,out SqlString output)

When output was long, an exception would be thrown similar to:

System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 28726 bytes to a T-SQL type with a smaller size limit of 8000 bytes.

Now you'd think that SqlString would map to varchar(max), if possible, but this is not the case. (I should also mention that the output is limited to 4000 bytes when using nvarchar).

The solution is to use SqlChars instead of SqlString - somewhere behind the scenes, this will get translated back to nvarchar, so the following will work:

DECLARE @output varchar(max)
EXEC DoSomething 'Loren Ipsum Dolar', @output output
SELECT @output

Of course you will have to convert your string to the SqlChars type before returning:

char[] chars = "Some String".ToCharArray();
output = new SqlChars(chars);       

Hope this helps!

8 comments:

Anonymous said...

SqlChars supposedly maps to nvarchar(max), but it seems to me to have a limit of 65535 characters. Agreed? Know a workaround?

Mark Boltuc said...

You could also do in c#

public static void foo([SqlFacet(MaxSize=-1)] out SqlString file)
{
file = superlongstring;
}

It will blow up on you if you give to big of a string, but it doesn't seem to be limited to the 65535 that the previous post indicated. I tried it with a string contain 1 million characters and it seemed to have returned it just fine.

CJ Randolph said...

FYI: SqlChars is limited to the maximum size of system.int32(2,147,483,647), which makes sense when you think about it as you can't specify an int index larger than the int type can handle.

Anonymous said...

It really helped me!!! Thanx from Mexico!

Anonymous said...

declare instead of varchar(max) to varchar(8000) then pass .

DECLARE @data VARCHAR(8000)

exec procesSp @data, output.
or

spfunction(@data)

Matt Marschall said...

Fantastic solved my large string problem. My Googlefu is helping me out today.

Anonymous said...

Thank you so much, works!
...
return new SqlChars(outStr.ToCharArray());

Anonymous said...

Thank you so much! Worked :)

return new SqlChars(outStr.ToCharArray());