Puis-je avoir un paramètre OUTPUT facultatif dans une procédure stockée?

Les parameters d'input et de sortie peuvent être affectés par défaut. Dans cet exemple:

 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 

le premier paramètre est requirejs, et le second et le troisième sont facultatifs – s'ils ne sont pas définis par le sous-programme appelant, les valeurs par défaut leur seront affectées. Essayez de déconner avec elle et la routine d'appel de test suivante dans SSMS en utilisant différentes valeurs et parameters pour voir comment tout cela fonctionne set.

 DECLARE @Output int SET @Output = 3 EXECUTE MyTest @Data1 = 1 ,@Data2 = 2 ,@Data3 = @Output output PRINT '---------' PRINT @Output 

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

Ce SP "jouet" interroge la valeur du paramètre OUTPUT , qu'il s'agisse de la valeur par défaut ou de la valeur NULL .

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

Si vous envoyez une valeur non initialisée (c'est-à-dire NULL ) pour la OUTPUT , vous avez vraiment NULL dans le SP, et non 0 . Fait sens, 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, cela a du sens, un paramètre est passé, et SP n'a pris aucune action explicite pour SET une valeur.

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

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

Maintenant, lorsqu'il est 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 parameters OUTPUT dans les SP.

Maintenant pour la torsion de l' insortinggue : La seule façon d'get la valeur par défaut "activer", est de ne pas passer du tout au paramètre OUTPUT , ce qui n'a que peu de sens: puisqu'il est paramétré comme paramètre OUTPUT , cela revient à returnner quelque chose. important "qui devrait être collecté.

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

donne cette sortie:

 yay its zero @QR=NULL 

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

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

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

 @AddressId int = -1 Output 

On dirait que c'est mauvais en termes de lisibilité puisque AddressId est ssortingctement conçu comme une variable OUTPUT . Mais ça fonctionne. S'il vous plaît laissez-moi savoir si vous avez une meilleure solution.

Ajoutant à ce que Philip a dit:

J'avais une procédure stockée dans ma database SQL Server qui ressemblait à ceci:

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

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

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

J'obtenais une exception System.Data.SqlClient.SqlException: La procédure ou la fonction attend le paramètre '@current_phase', qui n'était pas fourni.

J'utilise aussi cette fonction ailleurs dans mon programme et en passant un paramètre et en gérant le résultat. Pour ne pas avoir à modifier l'appel en cours, je viens de changer la procédure stockée pour rendre le paramètre de sortie également optionnel.

Donc, il ressemble maintenant à ce qui suit:

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

Comme vous exécutez une procédure stockée et non une instruction SQL, vous devez définir le type de command de votre command SQL sur Procédure stockée:

 cmd.CommandType = CommandType.StoredProcedure; 

Pris d' 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 … vous devez 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 renvoie «n / a» si le champ supplier_city contient une valeur null. Sinon, il returnnera la valeur supplier_city .