CODEWITHSUNDEEP
sqlsql-servert-sql
killerwild
killerwild

Reputation: 117

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

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.

Upvotes: 4

Views: 12947

Answers (4)

DBatesX
DBatesX

Reputation: 11

Along the lines of the methods r.net and Anand provided (which I also like to use), one way to be able to specifically set fields to NULL is to set a NULL token:

CREATE OR ALTER PROCEDURE MyTable_Post
        @Key int = NULL
    ,   @P1 int = NULL
    ,   @P2 varchar(100) = NULL
AS
DECLARE @NullToken varchar(10) = 'NULL';
UPDATE MyTable
SET     P1 = Coalesce(@P1, P1)
    ,   P2 = CASE  
            WHEN @P2 = @NullToken THEN NULL 
            WHEN @P2 IS NULL THEN P2
            ELSE @P2 
            END
WHERE Key = @Key
GO

EXEC MyTable_Post @Key = 1, @P2 = 'NULL'

In any case, calling the procedure with no parameters does nothing to the table, which is good in my opinion.

Of course, the caveat is that this only works with character values (nvarchar, varchar, etc) and not with others (bit, int, etc), but this method may be helpful in many cases.

Upvotes: 0

spencer741
spencer741

Reputation: 1185

After spending some time pondering this, I came up with a solution that doesn't use dynamic sql and also solves issues that coalese and isNull approaches don't solve. Note: minimal testing... looks like it does add a little overhead... but having this ability might allow flexibility on your client side code.

Table Schema:

FirstName NVARCHAR(200)
LastName NVARCHAR(200)
BirthDate DATE NULL
Activated BIT

Xml Param:

DECLARE @param = 
N'<FirstName>Foo</FirstName>
<LastName>Bar</LastName>
<BirthDate>1999-1-1</BirthDate>
<Activated>1</Activated>'

UPDATE [dbo].[UserMaster]
[FirstName] = case when @param.exist('/FirstName') = 1 then @param.value('/FirstName[1]','nvarchar(200)') else [FirstName] end,
[LastName] = case when @param.exist('/LastName') = 1 then @param.value('/LastName[1]','nvarchar(200)') else [LastName] end,
[BirthDate] = case when @param.exist('/BirthDate') = 1 then @param.value('/BirthDate[1]','date') else [BirthDate]end,
[Activated] = case when @param.exist('/Activated') = 1 then @param.value('/Activated[1]','bit') else [Activated] end
WHERE [UserMasterKey] = @param.value('/UserMasterKey[1]','bigint')

This will allow you to pass in an XML parameter, with any field you want and Update that field. Note that setting fields equal to themselves in this context, for example [FirstName] = [FirstName], is perfectly fine. The engine will optimize this away.

Now, this is a more complex solution to @Anand and @r.net 's answers, however it does solve the 'setting nullables' problem that some commenters pointed out.

I liked how elegant their solutions were, but wanted to be able to set NULL on nullable columns, while still retaining the ability to use the default field value if I don’t pass in a parameter for that field. The using null checks as the basis for defaults in this case fills the hole and digs another one, so to speak, because they don't allow one to explicitly update a field to null.

Passing an xml parameter allows you to do that, all without taking a dynamic sql approach.

I would also look into using Table-valued parameters, as I believe there could be a similar solution using those.

Upvotes: 0

Anand
Anand

Reputation: 1123

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

Upvotes: 1

r.net
r.net

Reputation: 631

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.

Upvotes: 13

Related Questions