Puis-je avoir un paramètre de sortie optionnel dans une procédure stockée?

Les paramètres de sortie et les valeurs par défaut ne fonctionnent pas bien ensemble! Ceci est de SQL 10.50.1617 (2008 R2). Ne vous laissez pas berner en croyant que cette construction fait magiquement un SET à cette valeur en votre nom (comme mon collègue l'a fait)!

Ce" jouet " SP interroge la valeur du paramètre OUTPUT, qu'il s'agisse de la valeur par défaut ou de 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 vous envoyez une valeur non initialisée (c'est-à-dire NULL) pour le OUTPUT, Vous avez vraiment NULL dans le SP, et non 0. C'est logique, quelque chose a été passé pour ce paramètre.

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

La sortie est:

wtf its NULL
@QR=NULL

Si nous ajoutons un SET explicite de l'appelant, nous obtenons:

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

Et la sortie (sans surprise):

@QR=999

Encore une fois, il est logique, un paramètre est passé, et SP n'a pas pris d'action explicite à {[10] } une valeur.

Ajoutez un SET du paramètre OUTPUT dans le SP (comme vous êtes censé le faire), mais Ne définissez rien de l'appelant:

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

Maintenant quand exécuté:

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

La sortie est:

wtf its NULL
@QR=1234

C'est le comportement "standard" pour la gestion des paramètres OUTPUT dans SPs.

Maintenant pour le plot twist : la seule façon d'obtenir la valeur par défaut pour "activer", est de ne pas passer le paramètre OUTPUT du tout , ce qui à mon humble avis n'a pas de sens: puisqu'il est configuré comme un paramètre OUTPUT, cela signifierait renvoyer quelque chose "important" qui devrait être collecté.

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

Donne ceci sortie:

yay its zero
@QR=NULL

Mais cela ne parvient pas à capturer la sortie SPs, probablement le but de ce SP pour commencer.

IMHO cette combinaison de fonctionnalités est une construction douteuse que je considérerais comme une odeur de code (ouf!!)

On dirait que je peux simplement ajouter une valeur par défaut au paramètre OUTPUT tel que:

@AddressId int = -1 Output

Semble être pauvre en termes de lisibilité puisque AddressId est conçu strictement comme une variable OUTPUT. Mais il fonctionne. Veuillez me faire savoir si vous avez une meilleure solution.

Ajoutant à ce que Philippe a dit:

J'avais une procédure stockée dans ma base de données sql server qui ressemblait à ce qui suit:

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

Et je l'appelais à partir de mon code. net comme suit:

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

Je recevais un système.Données.SqlClient.SqlException: la procédure ou la fonction attend le paramètre '@ current_phase', qui n'a pas été fourni.

J'utilise également cette fonction ailleurs dans mon programme et je passe un paramètre et gère celui de sortie. De sorte que je Je n'ai pas eu à modifier l'appel en cours que je faisais, je viens de changer la procédure stockée pour rendre le paramètre de sortie également facultatif.

Donc, il ressemble maintenant à ce qui suit:

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

Puisque vous exécutez une procédure stockée et non une instruction SQL, vous devez définir le type de commande de votre commande SQL Sur procédure stockée:

cmd.CommandType = CommandType.StoredProcedure;

Extrait de ici .

En outre, une fois que vous avez supprimé cette erreur, vous pouvez utiliser la fonction nvl() de SQL dans votre procédure pour spécifier ce que vous voulez afficher lorsqu'une valeur NULL est rencontrée.

Désolé de ne pas répondre correctement à la question...doit avoir mal compris. Voici un exemple de nvl, que je pense pourrait aborder un peu mieux?

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

L'instruction SQL ci-dessus retournerait 'n/a' si le champ supplier_city contenait une valeur null. Sinon, il retournerait la valeur supplier_city.