none
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?

    Friday, May 11, 2007 6:02 PM

Answers

  • 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
    Saturday, May 12, 2007 6:16 AM
    Moderator

All replies

  • 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
    Saturday, May 12, 2007 6:16 AM
    Moderator
  • 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!

    Monday, May 14, 2007 5:35 PM