April 7, 2009 at 8:15 am
Hi,
Here's my scenario: I want to update a row in my prices table 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.
April 7, 2009 at 8:21 am
IsNull (or Coalesce) is what you want. Take a look at them in Books Online.
The specific thing you're trying to do with them looks like:
UPDATE prices SET suppid = isnull(@suppid, suppid),
That way, if the variable is null, it will set the column to the value it already has.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 8:25 am
You can use NULL as default value for all parameters and then use case in the update statement. Your set clause should look like this:
…SET suppid = CASE WHEN @suppid IS NULL THEN suppid ELSE @suppid end,
partnum = CASE WHEN @partnum IS NULL THEN partnum ELSE @partnum end, …
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 7, 2009 at 9:06 am
Many thanks to the two posters, both solutions work fine and solve a problem I've been struggling with all afternoon! 😀
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply