SQL Server stored procedure with optional parameters updates invalid columns

Question

I have a (obsolete) VB6 program that needs some work. I have a stored procedure that updates the provider table. In this particular form, I do not need to update the entire row, only 10 or so columns from 20ish.

Here is some pseudo code that works fine if I want to update the entire line:

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 

If I only want to update some columns with data, here is the code (pseudo) that I tried (trying to use additional parameters):

 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 

and all this runs without errors, but it will update the wrong column, if I update one additional column, skip a few, and then update another optional column.

Example update using normal parameters:

 tblVendor ID: 2924 Vendor ID: Company1 Vendor Name: Company Name Contact: Bob email: [e-mail] 

An example of updating through optional parameters when I do not put a “contact”:

 tblVendor ID: 2924 Vendor ID: Company1 Vendor Name: Company Name Contact: [e-mail] email: [e-mail] 

SO updates the row, but updates the wrong column. What am I doing wrong?

Answer 1

You must explicitly specify parameter names when executing the stored procedure and pass null for those you want to exclude. Example

 exec spUpdateVendor2 @pID=102, @pVendorID = 1, @pVendorName = NULL, @pContact = 'Contact', @pEmail = NULL ...