sql server - stored - ¿Puedo tener un parámetro OUTPUT opcional en un procedimiento almacenado?

procedimiento almacenado para mostrar datos

Question

Tengo un procedimiento almacenado que tiene muchos parámetros de entrada y salida porque está insertando valores en varias tablas. En algunos casos, el proceso almacenado solo se inserta en una sola tabla (dependiendo de los parámetros de entrada). Aquí hay un escenario simulado para ilustrar.

Tablas / Objetos de datos:

Persona

Id
Name
Address

Nombre

Id
FirstName
LastName

Dirección

Id
Country
City

Supongamos que tengo un procedimiento almacenado que inserta a una persona. Si la dirección no existe, no la agregaré a la tabla de Address en la base de datos.

Por lo tanto, cuando genero el código para llamar al procedimiento almacenado, no quiero molestarme en agregar el parámetro Address . Para los parámetros INPUT , esto está bien porque SQL Server me permite suministrar valores predeterminados. Pero para el parámetro OUTPUT , ¿qué hago en el procedimiento almacenado para que sea opcional, así que no recibo un error ...

El procedimiento o la función ''Person_InsertPerson'' espera el parámetro ''@AddressId'', que no se suministró.

Answer 1

¡Los parámetros de salida y los valores predeterminados no funcionan bien juntos! Esto es de SQL 10.50.1617 (2008 R2). ¡No se dejen engañar por creer que este constructo mágicamente hace un SET con ese valor en su nombre (como lo hizo mi compañero de trabajo)!

Este SP de "juguete" interroga el valor del parámetro OUTPUT , ya sea el valor predeterminado o 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

Si envía un valor no inicializado (es decir, NULL ) para la OUTPUT , realmente obtuvo NULL dentro del SP, y no de 0 . Tiene sentido, algo pasó para ese parámetro.

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

salida es:

wtf its NULL
@QR=NULL

Si agregamos un SET explícito de la persona que llama, obtenemos:

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

y el resultado (no sorprendente):

@QR=999

De nuevo, tiene sentido, se pasa un parámetro y SP no tomó ninguna acción explícita para SET un valor.

Agregue un SET del parámetro OUTPUT en el SP (como se supone que debe hacer), pero no configure nada de la persona que llama:

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

Ahora cuando se ejecuta:

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

la salida es:

wtf its NULL
@QR=1234

Este es el comportamiento "estándar" para el manejo de parámetros OUTPUT en SP.

Ahora, para el giro de la trama : la única manera de "activar" el valor predeterminado es no pasar el parámetro OUTPUT en absoluto , lo que en mi humilde opinión tiene poco sentido: ya que está configurado como un parámetro OUTPUT , eso significaría devolver algo " importante "que debería ser recolectado".

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

da esta salida:

yay its zero
@QR=NULL

Pero esto no logra capturar la salida de los SP, presumiblemente el propósito de ese SP para empezar.

En mi humilde opinión, esta combinación de características es una construcción dudosa que consideraría un olor codificado (¡¡¡uf !!)

Answer 2

Agregando a lo que dijo Felipe:

Tenía un procedimiento almacenado en mi base de datos del servidor sql que se parecía a lo siguiente:

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

Y lo estaba llamando desde mi código .net como el siguiente:

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

Obtenía System.Data.SqlClient.SqlException: Procedimiento o función espera el parámetro ''@current_phase'', que no se proporcionó.

También estoy usando esta función en otro lugar de mi programa y paso un parámetro y manejo el de salida. Para no tener que modificar la llamada actual que estaba haciendo, simplemente cambié el procedimiento almacenado para hacer que el parámetro de salida también fuera opcional.

Entonces ahora se ve así:

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

Answer 3

Como está ejecutando un procedimiento almacenado y no una declaración SQL, debe establecer el tipo de comando de su Comando SQL en el Procedimiento almacenado:

cmd.CommandType = CommandType.StoredProcedure;

Tomado de here .

Además, una vez que elimine ese error, puede usar la función nvl() SQL en su procedimiento para especificar lo que desea mostrar cuando se encuentra un valor NULL.

Lamento no haber abordado la pregunta correctamente ... debe haberte entendido mal. Aquí hay un ejemplo de nvl, que creo que podría abordarlo un poco mejor.

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

La instrucción SQL anterior devolverá ''n / a'' si el campo supplier_city contiene un valor nulo. De lo contrario, devolvería el valor de supplier_city .

Answer 4

Parece que puedo agregar un valor predeterminado al parámetro OUTPUT , como:

@AddressId int = -1 Output

Parece que es pobre en términos de legibilidad, ya que AddressId se pretende estrictamente como una variable de OUTPUT . Pero funciona. Por favor, avíseme si tiene una mejor solución.

Answer 5

Se pueden asignar valores predeterminados tanto a los parámetros de entrada como a los de salida. En este ejemplo:

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

el primer parámetro es obligatorio y el segundo y el tercero son opcionales; si no lo establece la rutina de llamada, se les asignarán los valores predeterminados. Trate de jugar con él y la siguiente rutina de llamada de prueba en SSMS usando diferentes valores y configuraciones para ver cómo funciona todo junto.

DECLARE @Output int

SET @Output = 3

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

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