Specify SQL Type

• cgreathouse

  SSC Eights!

  Points: 855

  More actions

  May 1, 2014 at 5:00 pm

  #292935

  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!

• Solomon Rutzky

  SSCoach

  Points: 16430

  More actions

  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