August 28, 2009 at 4:52 am
Hi there
I have an sproc as follows:
dbo.RB_UpdateSequence
(
@ProdID int,
@sequence int,
@ModifiedBy int
)
AS
UPDATE RB_Products SET Sequence1 = @sequence, Amended_By=@ModifiedBy
WHERE Prod_ID = @ProdID
RETURN
However, I may need to update other columns instead of Sequence1 - i.e I may need to update Sequence2 instead. This will depend on the value of a parameter passed into the SPROC (TypeId). I have never used a CASE statement before but thought the following may work but it doesn't so my syntax is obviously wrong. Could someone please let me know where I'm going wrong in this? My code is:
dbo.RB_UpdateSequenceType
(
@ProdID int,
@sequence int,
@ModifiedBy int,
@TypeId int
)
AS
UPDATE RB_Products
SET
CASE @TypeId
WHEN 1 THEN Sequence1
WHEN 2 THEN Sequence2
END
= @sequence,
Amended_By=@ModifiedBy
WHERE Prod_ID = @ProdID
RETURN
Many thanks
Lorna
August 28, 2009 at 5:38 am
UPDATE needs to know which columns it is updating so the only way with static SQL is:
UPDATE RB_Products
SET Sequence1 =
CASE @TypeId
WHEN 1 THEN @sequence
ELSE Sequence1
END
,Sequence2 =
CASE @TypeId
WHEN 2 THEN @sequence
ELSE Sequence2
END
,Amended_By = @ModifiedBy
WHERE Prod_ID = @ProdID
September 2, 2009 at 6:15 am
First, I would not use sequence numbering as columns (check rules for normalized tables). I would have a sequence column and a sequence_nbr column to distinquish between the various sequences and products.
With the design change it is now possible to pass in a sequence_nbr along with the unique prod_id for update. I believe you want to update the sequence associated with a specifice prod_id and/or sequence number?
Now you can specify the row you want to update by including the prod_id and sequence_nbr in the stored procedure. This is how to use a case statement with an update DML.
------------
declare @LINKS table(
Prod_ID int,
sequence int,
sequence_nbr int,
Modified_By varchar(25),
Type_Id int
)
declare @ProdID int,
@sequence int,
@sequence_nbr int,
@ModifiedBy varchar(25),
@TypeId int
set @ProdID = 1
set @sequence = 3434
set @sequence_nbr = 1
set @ModifiedBy = 'exf'
set @TypeId = 3
INSERT INTO @LINKS VALUES (1,2322,1,'jlc',3)
INSERT INTO @LINKS VALUES (1,2322,2,'jlc',3)
UPDATE @LINKS
SET sequence = CASE WHEN @sequence_nbr = 1 THEN @Sequence
WHEN @sequence_nbr = 2 THEN @Sequence END,
Modified_By=@ModifiedBy
WHERE Prod_ID = @ProdID
AND sequence_nbr = @sequence_nbr
SELECT * FROM @LINKS
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply