Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
@AaronBertrand I call it from a case that returns single value, but even if I didn't scalar would still be okay as it is documented to return first value of first column and ignore the rest. Edited in version. –  GSerg Jul 26 '13 at 18:16
    
@AaronBertrand I believe you misread the code; TestTest is scalar and is called as such, WillBeCalledByCLR is TV and is also called as such (with exists), no CLR-specific magic here. As for fixes, yes, the company is slow about fixes because they are believed to not only fix, but also amend and brake. Would actually be nice if this behaviour was confirmed as nonexistent on 2012, as it was last time. –  GSerg Jul 26 '13 at 18:53
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.