C# CLR Stored Proc won't Deploy to SQL Server 2005

Question

I'm developing a C# SQL Server 2005 stored procedure that does data validation for my application. I have a nice framework built, that is working. The validation methods are along the lines of:

private void TestDate() {
    TestFields(delegate (string value) {
        if (value == String.Empty || value == "")
            return true;
        return IsDate(value);
    });
}

The solution compiles, deploys, and runs fine with several methods written like the above. TestFields iterates over the columns returned by a query, calling the delegate that returns whether or not the validity test is passed. I added a new method:

private void TestRequired() {
    TestFields(delegate (string value) {
        return ! (value == String.Empty || value == "");
    });
}

With this method, the DLL won't deploy: CREATE ASSEMBLY failed because method 'TestRequired' on type 'SurveyValidator' in safe assembly 'SurveyValidation' is storing to a static field. Storing to a static field is not allowed in safe assemblies.

I'm pulling out my hair. If I comment out TestRequired(), it works. Obviously, it's not doing an assignment statement, so I don't know what SQL Server is complaining about. Did I just stumble onto some kind of obscure bug? (I know what the error means, I don't have any static fields in the SP class. Just the static entry method that creating the project gives you.)

TIA, Dave

Answer 1

Add a CompilerGeneratedAttribute in front of your class declaration:

This attribute allows SQL server to reference compiler-generated static values.

I know it sounds a bit unorthodox, but is perfectly OK to do this.

Answer 2

I'll qualify this answer be saying I'm not too familiar with CLR SPs

Isn't value == String.Empty || value == "" a tautology, effectively testing the same thing twice? String.IsNullOrEmpty seems like a better option (however, that's another static reference... read on).

Counter to this, I'd be inclined to remove any static references. String.Empty seems like a potential candidate for this problem, so I'd remove it at least to test if it is causing the problem. So perhaps the test could be changed to only:

return value != "";

or perhaps:

return value != "" && value != null;