SQL Server stored procedure with optional parameters updates wrong columns

Question

I have a (legacy) VB6 program that needs some work. I have a stored procedure that updates a table of vendors. In this particular form, I don't need to update the entire row, just 10 or so columns out of the 20ish.

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

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 want to only update some of the columns with data here is the (pseudo) code I have been trying (attempt at using optional 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 it all runs w/o errors but it will update the wrong column if I update one optional column, skip a few, then update another optional column.

Example of update using normal parameters:

tblVendor
ID: 2924
Vendor ID: Company1
Vendor Name: Company Name
Contact: Bob
email: [email protected]

Example of updating via the optional parameters when I don't supply 'contact':

tblVendor
ID: 2924
Vendor ID: Company1
Vendor Name: Company Name
Contact: [email protected]
email: [email protected]

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

Solution

You have to explicitly specify parameter names when executing stored procedure and pass in null for those you want to leave out. Example

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

Comments

[WhatsThePoint]

by putting null when you execute the procedure doesnt it defeat the object of specifying it as optional to the procedure?

[Dimitri]

Not really. When you call the stored proc with optional parameters from your code, you can omit the optional parameter all together. and the stored proc will work. The requirement above is only true when executing the stored proc from SSMS. Without this, SQL Server wouldn't know which optional parameter you didn't pass. You have to explicitly state parameter names and the values you're passing.If that makes sense.