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

Question

J'ai une procédure stockée qui a un tas de paramètres d'entrée et de sortie parce qu'elle insère des valeurs dans plusieurs tables. Dans certains cas, la procédure stockée n'insère que dans une seule table (en fonction des paramètres d'entrée). Voici un scénario fictif pour illustrer mon propos.

Tableaux / Objets de données :

Personne

Id
Name
Address

Nom

Id
FirstName
LastName

Adresse

Id
Country
City

Disons que j'ai une procédure stockée qui insère une personne. Si l'adresse n'existe pas, je ne l'ajouterai pas à la base de données de la procédure. Address dans la base de données.

Ainsi, lorsque je génère le code pour appeler la procédure stockée, je ne veux pas prendre la peine d'ajouter l'élément Address paramètre. Pour INPUT paramètres, c'est correct car le serveur SQL me permet de fournir des valeurs par défaut. Mais pour les OUTPUT paramètre que dois-je faire dans la procédure stockée pour le rendre optionnel afin de ne pas recevoir une erreur...

*La procédure ou fonction 'Person_InsertPerson' attend le paramètre '@AddressId', qui n'a pas été fourni.*

Answer 1

Des valeurs par défaut peuvent être attribuées aux paramètres d'entrée et de sortie. 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 obligatoire, et le deuxième et le troisième sont optionnels - s'ils ne sont pas définis par la routine appelante, les valeurs par défaut leur seront attribuées. Essayez de vous amuser avec cette routine et la routine d'appel de test suivante dans SSMS en utilisant différentes valeurs et paramètres pour voir comment tout cela fonctionne.

DECLARE @Output int

SET @Output = 3

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

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

Answer 2

Il semble que je puisse simplement ajouter une valeur par défaut au paramètre OUTPUT, par exemple :

@AddressId int = -1 Sortie

Il semble qu'elle soit peu lisible, car AddressId est conçue strictement comme une variable de SORTIE. Mais cela fonctionne. Faites-moi savoir si vous avez une meilleure solution.

Merci.

Answer 3

Comme 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 ;

Tiré de http://www.eggheadcafe.com/community/aspnet/2/10146343/procedure-or-function-.aspx

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 avoir répondu correctement à la question... j'ai dû mal vous comprendre. Voici un exemple de nvl, qui pourrait, je pense, répondre un peu mieux à la question ?

sélectionnez NVL(ville_fournisseur, 'n/a') à partir de fournisseurs ;

L'instruction SQL ci-dessus renvoie "n/a" si le champ supplier_city contient une valeur nulle. Sinon, elle renvoie la valeur de la ville du fournisseur.

Answer 4

J'ajoute à ce que Philip 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'ai appelé depuis mon code .net comme suit :

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

J'obtenais une exception System.Data.SqlClient.SqlException : Procedure or function expects parameter '@current_phase', which was not supplied.

J'utilise également cette fonction ailleurs dans mon programme, en passant un paramètre et en traitant celui de sortie. Pour ne pas avoir à modifier l'appel en cours, j'ai simplement modifié la procédure stockée pour que le paramètre de sortie soit également facultatif.

Le résultat est donc le suivant :

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