Need workaround for 4000-character limit on CLR sproc parameters

Question

I've written a managed (C#) stored procedure with the following signature:

---

[Microsoft.SqlServer.Server.SqlProcedure]
public static void Sproc(string startDate, string endDate, string idList)...

---

Sometimes when I call this sproc, my comma-separated list of IDs exceeds 4000 characters.  How can I get around this problem? 

I guess I need something equivalent to NVarchar(MAX), but for CLR sprocs instead of TSQL.

Any thoughts?

Answer 1

Well, you could always catalog the assembly and the sproc manually, but I guess you want a solution from within VS?

In that case you have two options:
1. use the SqlTypes namespace and instead of strings as input params you use SqlChars for the params you think will go over 4K. That will make the deployment tool in VS catalog the sproc with a param(s) as nvarchar(max).
2. use the SqlFacet attribute and it's MaxSize property set to -1 for your params that you think will exceed 4K:
public static void Sproc([SqlFacet(MaxSize=-1)] string p1)...

Niels

Answer 2

Thanks, Niels.

Interestingly, I had already tried the SqlTypes.SqlChars type but ran into the same problem -- a 4000-character limit on the input parameter.

Adding the SqlFacet attribute did the trick, however.  Thank you!