By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

SP that updates a table according to optional parameters?

P: 3
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.
Apr 7 '09 #1
Share this Question
Share on Google+
5 Replies


P: 3
No worries, I have found the answer.
Apr 7 '09 #2

P: 31
Hi can u plz post the answer even I want to know
Apr 21 '09 #3

P: 3
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
Apr 21 '09 #4

P: 31
Hi thnks but can we write the above Update query as Dynamic Sql?
Apr 22 '09 #5

ck9663
Expert 2.5K+
P: 2,878
yes, build your t-sql string and use EXEC


-- CK
Apr 24 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.