SQL Server procedure update and coalesce issue

Question

So I am making a very simple update procedure, like I have done before, but when I run the execution through it, it does not update. I have looked this over several times and do not see any error in why it wouldn't cause the update to pass through it. My manual updates are working fine, so I am thinking I am either missing something super-obvious or this is something going on with the coalesce function. Any help would be great. The more eyes the merrier.

USE AdventureWorks2012
GO

CREATE PROCEDURE UpdateCreditCard
@CreditCardID INT,
@CardType nvarchar(50) = NULL,
@CardNumber nvarchar(25) = NULL,
@ExpMonth tinyint = NULL,
@ExpYear smallint = NULL
AS 
BEGIN

UPDATE [Sales].[CreditCard]
SET
    @CardType  = COALESCE(@CardType,CardType),
    @CardNumber  = COALESCE(@CardNumber,CardNumber),
    @ExpMonth  = COALESCE(@ExpMonth,ExpMonth),
    @ExpYear  = COALESCE(@ExpYear,ExpYear)
FROM Sales.CreditCard
WHERE @CreditCardID = CreditCardID
END

---

EXECUTE UpdateCreditCard 
@CreditCardID = 19267,
@CardType = 'MasterCard',
@CardNumber = '99999999',
@ExpMonth = 4,
@ExpYear = 2025

Answer 1

You are updating the variables not the columns in CreditCard table.

.....
SET
@CardType  = COALESCE(@CardType,CardType),   -- here you are updating the @CardType variable
.....

Try this.

CREATE PROCEDURE UpdateCreditCard
@CreditCardID INT,
@CardType nvarchar(50) = NULL,
@CardNumber nvarchar(25) = NULL,
@ExpMonth tinyint = NULL,
@ExpYear smallint = NULL
AS 
BEGIN

UPDATE [Sales].[CreditCard]
SET
    CardType  = COALESCE(@CardType,CardType),
    CardNumber  = COALESCE(@CardNumber,CardNumber),
    ExpMonth  = COALESCE(@ExpMonth,ExpMonth),
    ExpYear  = COALESCE(@ExpYear,ExpYear)
FROM Sales.CreditCard
WHERE   CreditCardID=@CreditCardID
END