Update Stored Procedure - only update certain fields and leave others as is

Question

I'm currently making a stored procedure which will update a products details. I want to make it (if possible) so that only the fields entered under 'values' column when being executed are updated and the rest remain the same as before.

ALTER PROCEDURE [dbo].[spUpdateProduct]
@ProductID int, @Brand nvarchar(30), @ModelNo nvarchar(9), @Description 
nvarchar(50), @CostPrice decimal(6,2), @Stock int, @SalePrice decimal(6,2)  

AS
BEGIN
SET NOCOUNT ON

UPDATE tblProduct 
SET                 
    Brand = @Brand,
    ModelNo = @ModelNo,
    [Description] = @Description,
    CostPrice = @CostPrice,
    Stock = @Stock,
    SalePrice = @SalePrice

WHERE ProductID = @ProductID 

END

This is currently what I have. When I go to change a value it errors saying I didn't enter a value for 'Brand' which is the next value after 'ProductID'.

Execute Window

Error when trying to update two fields (ProductID and CostPrice)

EDIT: The fields are all set to 'not null' when I created the table.

Answer 1

This is how I would do it. Have null-able parameters

ALTER PROCEDURE [dbo].[spUpdateProduct]
@ProductID int, 
@Brand nvarchar(30) = null, 
@ModelNo nvarchar(9) = null, ..... (to all the parameters except @ProductID)

AS
BEGIN
SET NOCOUNT ON

UPDATE tblProduct 
SET                 
    Brand = isNull(@Brand, Brand),
    ModelNo = isNull(@ModelNo, ModelNo),
    [Description] = isNull(@Description, Description),...


WHERE ProductID = @ProductID 

END

Basically you are only updating the fields if parameters are not null otherwise keeping old values.

Answer 2

You have two problems - (a) the parameter to the stored procedure must be provided (the error tells you this) and (b) what to do when the parameter is not provided. For the first problem, check the pass null value or use SQL query to execute stored procedure like so:

exec spUpdateProduct 1, null, null, null, 140.99, null, null

For problem (b), use coalesce to update based on value being passed:

ALTER PROCEDURE [dbo].[spUpdateProduct]
@ProductID int, @Brand nvarchar(30), @ModelNo nvarchar(9), @Description 
nvarchar(50), @CostPrice decimal(6,2), @Stock int, @SalePrice decimal(6,2)  

AS
BEGIN
SET NOCOUNT ON

UPDATE t 
SET                 
    Brand = coalesce(@Brand, t.Brand),
    ModelNo = coalesce(@ModelNo, t.ModelNo),
    [Description] = coalesce(@Description, t.Description),
    CostPrice = coalesce(@CostPrice, t.CostPrice),
    Stock = coalesce(@Stock, t.Stock),
    SalePrice = coalesce(@SalePrice, t.SalePrice)
FROM tblProduct as t
WHERE ProductID = @ProductID 

END