I have a CLR UDT function with the following signature:

[Microsoft.SqlServer.Server.SqlFunction(
DataAccess = DataAccessKind.Read,
FillRowMethodName = "LedgerDataFillRow",
Name = "PAS.Transfer.SunSystems.LedgerData",
SystemDataAccess = SystemDataAccessKind.None,
TableDefinition = SUN4_LEDGER_TABLE_DEFINITION
)]
public static IEnumerable LedgerData(SqlString server, SqlString database, 
SqlString businessUnit, SqlString ledger, SqlInt32 version) {

When I invoke it like follows it works no problem:

SELECT * FROM dbo.[PAS.Transfer.SunSystems.LedgerData]('XM4400', 'SUNDB', 
'644','A',4)

Problem is I want to pass some NULL values like follows:

SELECT * FROM dbo.[PAS.Transfer.SunSystems.LedgerData](NULL, 'SUNDB', 
'644','A',4)

Is this possible and if so how?  I cannot work around it by using empty 
strings because they have significant meaning in some scenarios.

VS2010/SQL2008/.NET3.5

Thanks in advance!

"Gary Fletcher" <GaryFletcher@discussions.microsoft.com> wrote in message 
news:E060BEAF-FE61-4892-83E6-6B67B1B22AD3@microsoft.com...
...
> public static IEnumerable LedgerData(SqlString server, SqlString database,
> SqlString businessUnit, SqlString ledger, SqlInt32 version) {
>
> When I invoke it like follows it works no problem:
>
> SELECT * FROM dbo.[PAS.Transfer.SunSystems.LedgerData]('XM4400', 'SUNDB',
> '644','A',4)
>
> Problem is I want to pass some NULL values like follows:
>


SqlString and SqlInt32 have "IsNull" properties which you can check.
e.g.

if (server.IsNull)
    .. do something


HTH,
Moe

Thanks Moe.  That was it.  

I overlooked the IsNull when Intellisense didn't show a HasValue property as 
one normally sees on a nullable type.  I assume the difference is for 
specific handling of the equivalent of DBNull.Value as one sees with the 
standard libraries.

On a side note:  you don't have to declare your parameters using SqlString, 
etc.  On a whim I tried using string, object, etc and they worked exactly as 
expected with no apparent side-effects.