Prev: vary the select statement in a from clause
Next: Que Assignments
From: Gary Fletcher on 15 Apr 2010 21:48
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!



From: Moe Sisko on 15 Apr 2010 23:23
"Gary Fletcher" <GaryFletcher(a)discussions.microsoft.com> wrote in message
news:E060BEAF-FE61-4892-83E6-6B67B1B22AD3(a)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



From: Gary Fletcher on 16 Apr 2010 10:03
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.
 | 
Pages: 1
Prev: vary the select statement in a from clause
Next: Que Assignments