Могу ли я иметь необязательный параметр OUTPUT в хранимой процедуре?

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

Таблицы/Объекты данных:

Person

Id
Name
Address

Имя

Id
FirstName
LastName

Адрес

Id
Country
City

Скажем, у меня есть хранимая процедура, которая вставляет человека. Если адрес не существует, я не буду добавлять его в таблицу Address в базе данных.

Таким образом, когда я генерирую код для вызова хранимой процедуры, я не хочу беспокоиться о добавлении параметра Address. Для параметров INPUT это нормально, потому что SQL Server позволяет мне устанавливать значения по умолчанию. Но для параметра OUTPUT, что я делаю в хранимой процедуре, чтобы сделать его необязательным, поэтому я не получаю сообщение об ошибке...

Процедура или функция 'Person_InsertPerson' ожидает параметра '@AddressId', который не был предоставлен.

Ответ 1

Оба входных и выходных параметра могут быть назначены по умолчанию. В этом примере:

CREATE PROCEDURE MyTest
  @Data1 int
 ,@Data2 int = 0
 ,@Data3 int = null output

AS

PRINT @Data1
PRINT @Data2
PRINT isnull(@Data3, -1)

SET @Data3 = @Data3 + 1

RETURN 0

требуется первый параметр, а второй и третий необязательны - если они не заданы вызывающей процедурой, им будут присвоены значения по умолчанию. Попробуйте возиться с ним и следующую процедуру тестового вызова в SSMS, используя разные значения и настройки, чтобы увидеть, как все это работает.

DECLARE @Output int

SET @Output = 3

EXECUTE MyTest
  @Data1 = 1
 ,@Data2 = 2
 ,@Data3 = @Output output

PRINT '---------'
PRINT @Output

Ответ 2

Выходные параметры и значения по умолчанию не работают вместе! Это от SQL 10.50.1617 (2008 R2). Не обманывайте себя, полагая, что эта конструкция магически делает SET для этого значения от вашего имени (например, мой коллега)!

Эта "игрушка" SP запрашивает значение параметра OUTPUT, независимо от того, является ли это значением по умолчанию или NULL.

CREATE PROCEDURE [dbo].[omgwtf] (@Qty INT, @QtyRetrieved INT = 0 OUTPUT)
AS
IF @QtyRetrieved = 0
BEGIN
    print 'yay its zero'
END
IF @QtyRetrieved is null
BEGIN
    print 'wtf its NULL'
END
RETURN

Если вы отправляете неинициализированное значение (т.е. NULL) для OUTPUT, вы действительно получили NULL внутри SP, а не 0. Имеет смысл, что-то получилось для этого параметра.

declare @QR int
exec [dbo].[omgwtf] 1, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

wtf its NULL
@QR=NULL

Если мы добавим явный SET из вызывающего, получим:

declare @QR int
set @QR = 999
exec [dbo].[omgwtf] 1, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

и (неудивительный) вывод:

@QR=999

Опять же, имеет смысл, передается параметр, и SP не принимал явного действия для SET значения.

Добавьте параметр SET параметра OUTPUT в SP (как и предполагалось), но ничего не устанавливайте у вызывающего:

ALTER PROCEDURE [dbo].[omgwtf] (@Qty INT, @QtyRetrieved INT = 0 OUTPUT)
AS
IF @QtyRetrieved = 0
BEGIN
    print 'yay its zero'
END
IF @QtyRetrieved is null
BEGIN
    print 'wtf its NULL'
END
SET @QtyRetrieved = @Qty
RETURN

Теперь, когда выполняется:

declare @QR int
exec [dbo].[omgwtf] 1234, @QR output
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

вывод:

wtf its NULL
@QR=1234

Это стандартное поведение для обработки параметров OUTPUT в SP.

Теперь для твита сюжета: единственный способ получить значение по умолчанию "активировать" - это не передать параметр OUTPUT вообще, который IMHO имеет мало смысла: поскольку он настроен как параметр OUTPUT, это означало бы возвращение чего-то "важного", которое должно быть собрано.

declare @QR int
exec [dbo].[omgwtf] 1
print '@QR=' + coalesce(convert(varchar, @QR),'NULL')

дает этот результат:

yay its zero
@QR=NULL

Но это не позволяет захватить вывод SP, предположительно, цель этого SP для начала.

ИМХО эта комбинация функций - сомнительная конструкция, я бы рассмотрел запах кода (фу!!!)

Ответ 3

Похоже, я могу просто добавить значение по умолчанию к параметру OUTPUT, например:

@AddressId int = -1 Output

Похоже, что он плохо читается с точки зрения удобочитаемости, поскольку AddressId используется строго как переменная OUTPUT. Но это работает. Пожалуйста, дайте мне знать, если у вас есть лучшее решение.

Ответ 4

Поскольку вы выполняете хранимую процедуру, а не инструкцию SQL, вам необходимо установить тип команды вашей SQL-команды в хранимую процедуру:

cmd.CommandType = CommandType.StoredProcedure;

Взято из здесь.

Кроме того, как только вы удалите эту ошибку, вы можете использовать функцию SQL nvl() в своей процедуре, чтобы указать, что вы хотите отображать, когда встречается значение NULL.

Извините за то, что вы неправильно решили вопрос... должно быть, неправильно поняли вас. Вот пример nvl, который, по моему мнению, может немного улучшить его?

select NVL(supplier_city, 'n/a')
from suppliers;

Вышеупомянутый оператор SQL вернет 'n/a', если поле supplier_city содержит нулевое значение. В противном случае оно вернет значение supplier_city.

Ответ 5

Добавив к тому, что сказал Филипп:

У меня была хранимая процедура в моей базе данных SQL Server, которая выглядела следующим образом:

dbo.<storedProcedure>
(@current_user char(8) = NULL,
@current_phase char(3) OUTPUT)

И я вызывал его из моего .net-кода следующим образом:

 DataTable dt = SqlClient.ExecuteDataTable(<connectionString>, <storedProcedure>);

Я получал System.Data.SqlClient.SqlException: процедура или функция ожидает параметра '@current_phase', который не был отправлен.

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

Итак, теперь он выглядит следующим образом:

dbo.<storedProcedure>
(@current_user char(8) = NULL,
@current_phase char(3) = NULL OUTPUT)