SQL Server Обработка нулевого ввода в пользовательской функции (UDF) среды CLR с помощью OnNullCall

У меня есть определяемая пользователем функция в SQL Server (написанная на .NET), которая очищает текст. Мне интересно, как обрабатывать нулевой ввод.

Вот функция на C #:

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlChars cleanEstActText(SqlChars input)
{
    SqlChars cascadingSqlChar = removeNBSP(input);
    cascadingSqlChar = optimizeFontTags(cascadingSqlChar);

    return cascadingSqlChar;
}

Это ошибка в SQL, если функция получает какие-либо нулевые данные:

A .NET Framework error occurred during execution of user-defined routine or aggregate "removeNBSP": 
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException: 
   at System.Data.SqlTypes.SqlChars.get_Value()
   at UserDefinedFunctions.removeNBSP(SqlChars input)

Чтение на SO и Google привело меня к атрибуту OnNullCall, который выглядит многообещающим.

Из MSDN:

Значение true, если метод вызывается, когда в вызове метода указаны входные аргументы null (Nothing в Visual Basic); false, если метод возвращает значение null (Nothing в Visual Basic), когда любой из его входных параметров имеет значение NULL (Nothing в Visual Basic).

Звучит именно так, как я хочу; если я получаю ноль, просто передаю ноль. Я не совсем уверен, как это реализовать, поэтому снова проверяю MSDN (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.server.sqlmethodattribute.aspx) и переписываю первую строку моя функция от

[Microsoft.SqlServer.Server.SqlFunction]

to

[Microsoft.SqlServer.Server.SqlMethod(OnNullCall = false, IsMutator = false, InvokeIfReceiverIsNull = false)]

Если я сделаю это, я получаю сообщение об ошибке в SQL каждый раз, когда использую его:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.cleanEstActText", or the name is ambiguous.

Я неправильно реализую OnNullCall? Должен ли я делать что-нибудь еще? Есть ли действительно хороший способ заставить мою функцию передавать нули?


null sql-server user-defined-functions sqlclr
person jtpereyda    schedule 27.12.2010    source источник

Ответы (4)


arrow_upward
9
arrow_downward

Вы можете попробовать это

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlChars cleanEstActText(SqlChars input)
{

    if (input.IsNull) return null;

    SqlChars cascadingSqlChar = removeNBSP(input);
    cascadingSqlChar = optimizeFontTags(cascadingSqlChar);

    return cascadingSqlChar;
}

Все типы SqlData, допускающие значение NULL, имеют свойство IsNull.

Спасибо, Хари

person Hari Sandeep Gutta    schedule 08.04.2011
comment
Приведенные выше два решения - это просто обходные пути, которые не решают проблему. Ответ Хари правильный. - person alphadogg; 25.10.2011
comment
@alphadogg и Хари, это на самом деле неправильный ответ. По крайней мере, не в контексте вопроса. Это правильный ответ, если имеется несколько входных параметров и хотя бы один из них может корректно передавать значение NULL. В противном случае вы должны использовать WITH RETURNS NULL ON NULL INPUT в CREATE FUNCTION, как описано в моем ответе. - person Solomon Rutzky; 25.02.2015
comment
if (input.IsNull) return null; - коротко и просто! Работает, когда тип возврата тоже string, а не SqlChars. - person James L.; 14.08.2019

arrow_upward
7
arrow_downward

Принятый ответ неверен, хотя технически он работает. Проблема с проверкой NULL в самом коде заключается в том, что код вызывается и должен выполнить эту проверку. Это требуется только в том случае, если вы хотите разрешить одному или нескольким параметрам передавать действительный NULL, не вызывая пропуска выполнения метода.

Это определенно возможно, но, к сожалению, не с помощью механизма публикации Visual Studio / SSDT, который создает для вас весь T-SQL. Для этого вам необходимо:

  • Вручную развернуть оператор T-SQL CREATE FUNCTION
  • Выполните ALTER FUNCTION после публикации кода в SQL Server

В любом случае синтаксис для этого, как описано на странице MSDN для СОЗДАТЬ ФУНКЦИЮ, это: WITH RETURNS NULL ON NULL INPUT.

Чтобы выразить это в полном контексте:

CREATE FUNCTION SchemaName.FunctionName ( { parameter_list } )
RETURNS DataType
WITH RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME ...

Опять же, имейте в виду, что если этот параметр указан, то любой входной параметр, равный NULL, вызовет пропуск функции и возвратит NULL.

ОБНОВЛЕНИЕ:
Проголосуйте за следующее предложение Microsoft Connect, чтобы надеюсь добавить поддержку свойства OnNullCall атрибута SqlFunction:

Реализовать свойство OnNullCall для SqlFunctionAttribute ВОЗВРАЩАЕТ НУЛЬ ПРИ НУЛЕВОМ ВХОДЕ

person Solomon Rutzky    schedule 25.02.2015

arrow_upward
1
arrow_downward

@Josh, как бы то ни было, я вызываю свои функции CLR, оборачивая все параметры функцией объединения. Итак, что-то вроде select myFunc(coalesce(fld1,'')). Затем в моей функции CLR я проверяю значения параметров в самом верху, что-то вроде if (param1.ToString() == '') return SqlString.Null. Конечно, вы можете делать все, что вам нужно, внутри функции, но это общий шаблон, который я использовал, чтобы обойти нулевую проблему с процессами / функциями CLR. Мучительно не забывать оборачивать их каждый раз, когда я их использую, но это работает.

person sisdog    schedule 19.03.2011
comment
Это кажется довольно полезным. Единственная проблема заключается в том, что, возможно, нужно различать Null и ''. Однако я думаю, что в моем случае изменение пустых строк на Null будет работать так же. Спасибо. - person jtpereyda; 21.03.2011
comment
@EvilDr На самом деле это плохой, запутанный и ненужный шаблон. Если вам нужно разрешить NULL значения в вашем коде хотя бы для одного входного параметра, вы можете просто протестировать любые / все входные параметры, используя свойство IsNull, которое есть у всех SqlTypes. Если НИ ОДИН из входных параметров никогда не передается в ПУСТО (NULL), то вы можете сначала пропустить вызов метода, указав WITH RETURNS NULL ON NULL INPUT. Подробности см. В моем ответе. - person Solomon Rutzky; 04.06.2015
comment
Собственно да, согласен. После дополнительного чтения и рассмотрения ответ относительно IF value.IsNull в сочетании с If String.IsNullOrWhitespace был идеальным. Ваш общий вклад по каждому пункту здесь выдающийся. - person EvilDr; 04.06.2015

arrow_upward
-1
arrow_downward

Изменить: это было написано до принятого выше ответа. Смотри вместо этого.

Я все еще убежден, что есть способ сделать это, но я его не нашел (в любом случае я недостаточно знаю, как SQL взаимодействует с CLR).

Чтобы обойти эту проблему, я сделал довольно очевидную вещь: проверил наличие нулей.

Select dbo.cleanEstActText(EstActText1)
From BLEstActivity
Where EstActText1 is not NULL
person jtpereyda    schedule 20.01.2011