OUTPUT-параметры хранимой процедуры SQL Server в ASP.NET

Зачем нужны выходные параметры

Типичная ситуация: процедура вставляет заказ и должна сообщить приложению его новый номер. Или процедура проверяет логин и возвращает идентификатор пользователя вместе с признаком блокировки. Можно оформить ответ как SELECT из одной строки, но тогда приходится открывать DataReader или DataTable ради пары значений.

OUTPUT-параметры решают это изящнее. Значение передаётся через тот же объект SqlParameter, который вы добавили в команду, и читается после выполнения одной строкой кода. Набор строк при этом остаётся свободен для основных данных: процедура может одновременно вернуть список товаров и общее их количество в выходном параметре.

Процедура на стороне SQL Server

В объявлении процедуры выходной параметр помечается ключевым словом OUTPUT. Внутри ему присваивают значение как обычной переменной. Помимо этого, у любой процедуры есть код возврата: целое число из оператора RETURN. Его принято использовать как статус выполнения, а данные передавать через OUTPUT.

CREATE PROCEDURE dbo.AddCategory
    @CategoryName nvarchar(15),
    @Description  ntext = NULL,
    @CategoryID   int OUTPUT          -- сюда вернём новый ключ
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT 1 FROM Categories WHERE CategoryName = @CategoryName)
        RETURN 1;                     -- статус: такая категория уже есть

    INSERT INTO Categories (CategoryName, Description)
    VALUES (@CategoryName, @Description);

    SET @CategoryID = SCOPE_IDENTITY();
    RETURN 0;                         -- успех
END

Объявляем параметры в ADO.NET

На стороне приложения команде нужен CommandType.StoredProcedure и набор параметров. Входные добавляются как обычно. Для выходного указываем Direction = ParameterDirection.Output, а для кода возврата ParameterDirection.ReturnValue. Имя параметра кода возврата может быть любым, но принято @RETURN_VALUE.

Для строковых выходных параметров обязательно задавайте Size. Без него ADO.NET выбросит исключение о нулевом размере строки, потому что не знает, сколько места выделить под ответ.

Imports System.Data
Imports System.Data.SqlClient

Public Function AddCategory(name As String, ByRef newId As Integer) As Boolean
    Using cn As New SqlConnection(ConnectionString), _
          cmd As New SqlCommand("dbo.AddCategory", cn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15).Value = name

        Dim pId = cmd.Parameters.Add("@CategoryID", SqlDbType.Int)
        pId.Direction = ParameterDirection.Output

        Dim pRet = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int)
        pRet.Direction = ParameterDirection.ReturnValue

        cn.Open()
        cmd.ExecuteNonQuery()

        If CInt(pRet.Value) <> 0 Then Return False   ' дубликат
        newId = CInt(pId.Value)
        Return True
    End Using
End Function

Когда значение становится доступно

С ExecuteNonQuery и ExecuteScalar всё просто: после возврата из метода выходные параметры уже заполнены. С DataReader иначе, и на этом спотыкаются чаще всего. SQL Server отправляет значения OUTPUT-параметров в самом конце ответа, после всех наборов строк. Пока DataReader открыт, свойство Value содержит Nothing.

Порядок должен быть таким: прочитать все строки, закрыть reader вызовом Close или выходом из блока Using, и только потом обращаться к параметру. То же касается DataAdapter.Fill: он сам дочитывает и закрывает reader, так что после Fill параметры заполнены.

' Процедура возвращает страницу товаров и общее число в @Total
Dim pTotal = cmd.Parameters.Add("@Total", SqlDbType.Int)
pTotal.Direction = ParameterDirection.Output

Dim items As New List(Of Product)
Using rdr = cmd.ExecuteReader()
    While rdr.Read()
        items.Add(New Product With {
            .Id = rdr.GetInt32(0), .Name = rdr.GetString(1)})
    End While
End Using                         ' reader закрыт, параметр заполнен

Dim total = If(pTotal.Value Is DBNull.Value, 0, CInt(pTotal.Value))

Output или InputOutput

В T-SQL нет отдельного вида «только выход»: параметр с OUTPUT всегда ещё и входной. ADO.NET различает два режима. При ParameterDirection.Output значение из приложения не передаётся, и внутри процедуры параметр стартует с NULL. При InputOutput вы задаёте начальное значение, процедура может его прочитать и изменить.

Это полезно для счётчиков и курсоров постраничной выборки: приложение передаёт номер последней загруженной записи, процедура возвращает следующий. Если же процедура только пишет в параметр, используйте Output, так намерение понятнее.

Типичные ошибки

Список проблем, которые встречаются при работе с выходными параметрами чаще всего:

• Value равно Nothing: значение читается до закрытия DataReader;
• исключение «String[n]: the Size property has an invalid size of 0»: у строкового выходного параметра не задан Size;
• InvalidCastException при CInt: процедура не присвоила значение, и пришёл DBNull, проверяйте его перед преобразованием;
• код возврата всегда 0: забыт параметр с ReturnValue или процедура завершается без RETURN;
• «Procedure expects parameter which was not supplied»: выходной параметр объявлен в процедуре без значения по умолчанию, но не добавлен в команду.

Как это делают сегодня

Вопросы и ответы