SQL Server procédure stockée avec des paramètres optionnels mises à jour de faux colonnes
J'ai un (ancien) VB6 programme qui a besoin de quelques travaux. J'ai une procédure stockée qui met à jour une table des vendeurs. Dans cette forme particulière, je n'ai pas besoin de mettre à jour l'ensemble de la ligne, seulement 10 ou si des colonnes de la 20ish.
Voici le pseudo-code qui fonctionne très bien si je veux mettre à jour l'ensemble de la ligne:
CREATE PROCEDURE [dbo].[spUpdateVendor](
@pID INT,
@pVendorID varchar(254),
@pVendorName varchar(255),
@pContact varchar(255),
@pEmail varchar(255),
...)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON
DECLARE @ErrorMessage nvarchar(4000);
BEGIN TRY
-- Start the transaction
BEGIN TRANSACTION
UPDATE tblVendor
SET
[Vendor ID] = @pVendorID,
[Vendor Name] = @pVendorName,
[Contact] = @pContact,
[email] = @pEmail
...
WHERE
[ID] = @pID
COMMIT TRANSACTION;
END TRY
Si je veux seulement mettre à jour certaines des colonnes avec des données ici est la (pseudo -) code que j'ai essayé (tentative d'utiliser les paramètres facultatifs):
CREATE PROCEDURE [dbo].[spUpdateVendor2](
@pID INT,
@pVendorID varchar(254) = NULL,
@pVendorName varchar(255) = NULL,
@pContact varchar(255) = NULL,
@pEmail varchar(255) = NULL,
...)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON
DECLARE @ErrorMessage nvarchar(4000);
BEGIN TRY
-- Start the transaction
BEGIN TRANSACTION
UPDATE tblVendor
SET
[Vendor ID] = ISNULL(@pVendorID,[Vendor ID]),
[Vendor Name] = ISNULL(@pVendorName,[Vendor Name]),
[Contact] = ISNULL(@pContact,[Contact]),
[Email] = ISNULL(@pEmail,[email]),
...
WHERE
[ID] = @pID
COMMIT TRANSACTION;
END TRY
et tout fonctionne w/o des erreurs, mais il mettra à jour la mauvaise colonne si je mettre à jour une colonne facultatif, passez un peu, puis mettre à jour une autre option de la colonne.
Exemple de mise à jour à l'aide des paramètres normaux:
tblVendor
ID: 2924
Vendor ID: Company1
Vendor Name: Company Name
Contact: Bob
email: bob@company.com
Exemple de la mise à jour via les paramètres facultatifs, je n'ai pas une alimentation "contact":
tblVendor
ID: 2924
Vendor ID: Company1
Vendor Name: Company Name
Contact: bob@company.com
email: bob@company.com
De SORTE qu'il met à jour la ligne, mais il met à jour la mauvaise colonne. Ce que je fais mal?
OriginalL'auteur Aaron Siemieniec | 2012-10-24
Vous devez vous connecter pour publier un commentaire.
Vous devez spécifier explicitement les noms des paramètres lors de l'exécution de la procédure stockée et passer la valeur null pour ceux que vous voulez exclure. Exemple
en mettant
null
lorsque vous exécutez la procédure ne marche pas contraires à l'objet de la spécification optionnelle à la procédure?Pas vraiment. Lorsque vous appelez la procédure stockée avec des paramètres optionnels à partir de votre code, vous pouvez omettre le paramètre facultatif tous ensemble. et la stockées traitement va fonctionner. L'exigence ci-dessus n'est vrai que lors de l'exécution de la procédure stockée à partir de SSMS. Sans cela, SQL Server ne sais pas quel paramètre facultatif vous n'avez pas passer. Vous devez indiquer explicitement les noms de paramètres et les valeurs que vous soyez simplement de passage.Si cela fait sens.
OriginalL'auteur Dimitri