SP that updates a table according to optional parameters?

[jimworley]

Hi,

Here's my scenario: I want to update a row using an SP. Sometimes I want to update the whole row, sometimes just a couple of fields in the row. I don't want to write an SP for every scenario, I want to write one that knows only to update the fields for which I have passed in parameters containing data.

I have got so far with the SP:

CREATE PROCEDURE [dbo].[usp_updateprice]
@suppid varchar(3),
@partnum varchar(30),
@description varchar(60),
@xrefnum varchar(30),
@nato_code varchar(22),
@reconind varchar(1),
@discode varchar(2),
@np_discode varchar(2),
@minordq int,
@current_price money,
@price_annex_f money,
@price_am money,
@price_painted money,
@surcharge money,
@flagtext varchar(245)
AS
BEGIN
UPDATE prices SET suppid = @suppid,
partnum = @partnum,
description = @description,
xrefnum = @xrefnum,
nato_code = @nato_code,
reconind = @reconind,
discode = @discode,
np_discode = @np_discode,
minordq = @minordq,
current_price = @current_price,
price_annex_f = @price_annex_f,
price_am = @price_am,
price_painted = @price_painted,
surcharge = @surcharge,
flagtext = @flagtext
WHERE suppid = @suppid AND partnum = @partnum
END

Anyone have any ideas as to how this could be accomplished? I am calling the SP with VB.net

Many thanks in advance.

[jimworley]

No worries, I have found the answer.

[SnehaAgrawal]

Hi can u plz post the answer even I want to know

[jimworley]

OK, this SP takes some parameters and updates a record with them. If any of the parameters that are passed to the procedure are NULL then the original values are left unchanged in the target field. The SP passes back the rowcount of rows updated which is useful to know.


GO
CREATE PROCEDURE [dbo].[usp_updateprice]
-- Incoming Parameters
@suppid varchar(3),
@partnum varchar(30),
@description varchar(60),
@current_price money
AS
BEGIN
UPDATE prices SET suppid = isnull(@suppid, suppid),
partnum = isnull(@partnum, partnum),
description = isnull(@description, description),
current_price = isnull(@current_price,current_price)
WHERE suppid = @suppid AND partnum = @partnum
END

return @@rowcount

[SnehaAgrawal]

Hi thnks but can we write the above Update query as Dynamic Sql?

[ck9663]

yes, build your t-sql string and use EXEC


-- CK