как сообщить об ошибке из пользовательской функции sql server

<p>я пишу пользовательскую функцию в sql server 2008. я знаю, что функции не могут вызывать ошибки обычным способом - если вы попытаетесь включить инструкцию raiserror, sql возвращает:</p><pre><code>msg 443, level 16, state 14, procedure ..., line ... invalid use of a side-effecting operator 'raiserror' within a function. </code></pre><p>но дело в том, что функция принимает некоторый ввод, который может быть недействительным, и, если это так, нет значимого значения, которое функция может вернуть. что мне тогда делать?</p><p>я мог бы, конечно, вернуть null, но для любого разработчика было бы трудно устранить эту проблему. я также мог бы вызвать деление на ноль или что-то в этом роде - это сгенерировало бы сообщение об ошибке, но вводящее в заблуждение могу ли я как-нибудь сообщить о своем сообщении об ошибке?</p>

<p>вы можете использовать cast, чтобы выдать значимую ошибку:</p><pre><code>create function dbo.throwerror() returns nvarchar(max) as begin return cast('error happened here.' as int); end </code></pre><p>затем sql server покажет некоторую справочную информацию:</p><pre><code>msg 245, level 16, state 1, line 1 conversion failed when converting the varchar value 'error happened here.' to data type int. </code></pre>

<p>обычный трюк состоит в том, чтобы форсировать деление на 0. это вызовет ошибку и прервет текущий оператор, который оценивает функцию. если разработчик или специалист по поддержке знает об этом поведении, расследование и устранение неполадок довольно просто, поскольку ошибка деления на 0 понимается как признак другой, не связанной проблемы.</p><p>как бы плохо это ни выглядело с любой точки зрения, к сожалению, дизайн функций sql на данный момент не дает лучшего выбора. использование raiserror должно быть абсолютно разрешено в функциях.</p>

<p>исходя из ответа владимира королева, условно выдать ошибку</p><pre><code>create function [dbo].[throw] ( @error nvarchar(max) ) returns bit as begin return cast(@error as int) end go declare @error nvarchar(max) declare @bit bit if `error condition` set @error = 'my error' else set @error = '0' set @bit = [dbo].[throw](@error) </code></pre>

<p>я думаю, что самый простой способ - просто принять, что функция может возвращать null, если переданы недопустимые аргументы. пока это четко задокументировано, тогда это должно быть хорошо?</p><pre><code>-- ============================================= -- author: am -- create date: 03/02/2010 -- description: returns the appropriate exchange rate -- based on the input parameters. -- if the rate cannot be found, returns null -- (raiseerror can't be used in udfs) -- ============================================= alter function [dbo].[getexchangerate] ( @currencyfrom char(3), @currencyto char(3), @ondate date ) returns decimal(18,4) as begin declare @closingrate as decimal(18,4) select top 1 @closingrate=closingrate from [factcurrencyrate] where fromcurrencycode=@currencyfrom and tocurrencycode=@currencyto and dateid=dbo.datetointegerkey(@ondate) return @closingrate end go </code></pre>

<p><code>raiseerror</code> или <code>@@error</code> недопустимы в udf. можете ли вы превратить udf в пошаговую процедуру?</p><p>из статьи эрланда соммарскога <a href="http://www.sommarskog.se/error-handling-i.html#udf">обработка ошибок в sql server - фон</a> :</p><blockquote> <p>пользовательские функции обычно вызываются как часть инструкций set, select, insert, update или delete. я обнаружил, что если в многозначной табличной функции или в скалярной функции возникает ошибка, то выполнение функции немедленно прерывается, как и утверждение, частью которого является функция. выполнение продолжается на следующей строке, если ошибка не прервала пакет. в любом случае @@ error равен 0. таким образом, нет способа обнаружить, что произошла ошибка в функции из t-sql.</p> <p>проблема не возникает с встроенными табличными функциями, так как встроенная табличная функция является в основном макросом, который обработчик запросов вставляет в запрос.</p> <p>вы также можете выполнять скалярные функции с помощью оператора exec. в этом случае выполнение продолжается, если возникает ошибка (если только это не ошибка прерывания пакета). @@ error установлен, и вы можете проверить значение @@ error в функции. однако может быть проблематично сообщить об ошибке вызывающей стороне.</p> </blockquote>

<p>верхний ответ обычно лучший, но он не работает для встроенных табличных функций.</p><p>miketeevee дал решение для этого в своем комментарии к верхнему ответу, но для этого требовалось использовать агрегатную функцию, такую как max, которая не работала в моих обстоятельствах.</p><p>я возился с альтернативным решением для случая, когда вам нужна встроенная таблица со значением udf, которая вместо этого возвращает что-то вроде <strong><em> select * </em></strong> совокупности. пример кода для решения этого конкретного случая приведен ниже. как кто-то уже указал ... <strong><em> "jeez wotta hack" </em></strong> :) я приветствую любое лучшее решение для этого случая!</p><pre><code>create table foo ( id nvarchar(255), data nvarchar(255) ) go insert into foo (id, data) values ('green eggs', 'ham') go create function dbo.getfoo(@aid nvarchar(255)) returns table as return ( select *, 0 as causeserror from foo where id = @aid --error checking code is embedded within this union --when the id exists, this second selection is empty due to where clause at end --when id doesn't exist, invalid cast with case statement conditionally causes an error --case statement is very hack-y, but this was the only way i could get the code to compile --for an inline tvf --simpler approaches were caught at compile time by sql server union select top 1 *, case when ((select top 1 id from foo where id = @aid) = @aid) then 0 else 'error in getfoo() - id "' + isnull(@aid, 'null') + '" does not exist' end from foo where (not exists (select id from foo where id = @aid)) ) go --this does not cause an error select * from dbo.getfoo('green eggs') go --this does cause an error select * from dbo.getfoo('yellow eggs') go drop function dbo.getfoo go drop table foo go </code></pre>

<p>несколько человек спрашивали о возникновении ошибок в табличных функциях, так как вы не можете использовать "<strong>return [invalid cast]</strong>" вещи. присвоение неверного приведения переменной также работает.</p><pre class="lang-sql prettyprint-override"><code>create function fn() returns @t table (col char) as begin declare @i int = cast('booooom!' as int) return end </code></pre><p>это приводит к:</p><blockquote> <p>сообщение 245, уровень 16, состояние 1, строка 14 преобразование не удалось при преобразовании значения varchar 'booooom!' к типу данных int.</p> </blockquote>

<p>я не могу комментировать ответ davec относительно табличной функции, но, по моему скромному мнению, это более простое решение:</p><pre class="lang-sql prettyprint-override"><code>create function dbo.ufn\_test (@a tinyint) returns @returns table(column1 varchar(10), value1 tinyint) begin if @a>50 -- if @a > 50 - raise an error begin insert into @returns (column1, value1) values('error','@a is bigger than 50!') -- reminder value1 should be tinyint end insert into @returns (column1, value1) values('something',@a) return; end select column1, value1 from dbo.ufn\_test(1) -- this is okay select column1, value1 from dbo.ufn\_test(51) -- this will raise an error </code></pre>