Consider a CLR stored procedure that accepts an XML parameter and calls another stored procedure in the same schema:
[SqlFunction(DataAccess = DataAccessKind.Read,
IsDeterministic = false, IsPrecise = true,
SystemDataAccess = SystemDataAccessKind.Read)]
public static SqlBoolean TestTest(SqlXml Data)
{
using (var c = new SqlConnection("context connection=true"))
{
c.Open();
using (var cmd = new SqlCommand(@"select case when exists(select 0 from [testing].WillBeCalledByCLR(@d)) then 1 else 0 end;", c))
{
var p = cmd.Parameters.Add("@d", SqlDbType.Xml);
p.Direction = ParameterDirection.Input;
if (Data.IsNull)
{ p.Value = DBNull.Value; }
else
{ p.Value = Data; } // Or Data.Value
return (bool)cmd.ExecuteScalar();
}
}
}
This CLR function is visible in SQL as:
create function [testing].[TestTest] ( @data xml )
returns bit
WITH CALLED ON NULL INPUT
AS
EXTERNAL NAME [Test].[Test.Test].[TestTest]
The stored function it calls is this:
create function testing.WillBeCalledByCLR (@x xml = null)
returns table
as
return (
select 1 as one, 2 as two, 'three' as three
);
In this setup, if I call the CLR function like this:
if testing.TestTest(null) = 1
begin
select 'Meaningful actions';
end;
or like this:
declare @res bit = testing.TestTest(null);
or like this:
declare @res bit;
set @res = testing.TestTest(null);
or like this:
declare @res bit;
select @res = testing.TestTest(null);
then I get:
Msg 2905, Level 25, State 1, Line 6
Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
But if I call it like this:
select testing.TestTest(null);
or like this:
declare @res bit;
set @res = (select testing.TestTest(null));
I get a proper value back (e.g. 1
).
If, instead of null
, I pass an empty string ''
, the function is successfully called in all cases.
Why? Did I do something wrong in my CLR function?
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) (VM)