Home Forums Programming CLR Integration and Programming. Specify SQL Type RE: Specify SQL Type

  • May 1, 2014 at 5:24 pm

    #1710228

    cgreathouse (5/1/2014)

    Hello

    I have a CLR SQL Function with a signature that looks like this...

    public static long fnCreateKey(string value)

    {

    ...

    }

    The function is working fine. However, in SSMS this is what the signature looks like...

    [dbo].[fnCreateKey](@value [nvarchar](4000))

    That function shouldn't get anything larger than varchar(9). Is there a way to change the type from a nvarchar to a varchar and change the length from 4000 to 9?

    Thanks!

    Hey there. A few things to mention:

    • Use SqlFacet to tell Visual Studio how to generate certain properties of input/output params. In this case: fnCreateKey([SqlFacet(MaxSize = 9)] SqlString value)
    • You should use the Sql types instead of native .Net types for input/output params. So use either SqlString or SqlChars instead of string.
    • No, you cannot do VARCHAR. NVARCHAR / NCHAR is the only option with SQLCLR

    Take care,

    Solomon...

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR