SQL 2005 .NET Stored Procedure - Returning more than 8000 chars i a variable

Question

I have developed a .NET Stored Procedure in C# (SQL 2005 sept CTP, VS 2005
RC1).

1. I'm passing an T-SQL output variable into the .NET function. For example
@TSQL varchar(max)
2. Inside the .NET procedure the variable is of type string (for example:
ref string SQLText).
3. The .NET procedure populates my local variable SQLText with a quite long
SQL Statement which I want to return to my T-SQL Code into the @TSQL
variable. Everything is OK if my SQLText variable is less than 8000 chars
long. If > 8000 I get an error stating the variable is too long.

Is there a limitation returning long variables OUT of .NET SQL Stored
Procedures? Passing more than 8000 chars IN is perfectly ok...

Thanks!

Answer 1

I believe you are using Visual Studio to deploy this code. By default Visual Studio uses nvarchar(4000) for string, SqlString, char[] and nvarchar(MAX) for SqlChars.
So using SqlChars would solve your problem.
If you want to use string, you can deploy manually using Create Function statement in SQL Server. Alternatively if you want to automate in Visual Studio, you can use post-deploy script to issue an ALTER FUNCTION statement.

Thanks,
-Vineet.