May 10, 2016 at 2:26 pm
Hi All,
I am passing a parameter as @col1. I need to set col3 to 100 if @col1 =1 else col3 will be any value that is passed in. Is there any way, I can remove the else statement and write one update statement without if and else.
Create procedure Test
(
@col1 int,
@col2 int
)
AS
If @col1 =1
Update TestTable
set col3 = 100
else if @col1 is anything else but 1 then
Update TestTable
set @col3 = @col1
May 10, 2016 at 2:29 pm
anjaliagarwal5 (5/10/2016)
Hi All,I am passing a parameter as @col1. I need to set col3 to 100 if @col1 =1 else col3 will be any value that is passed in. Is there any way, I can remove the else statement and write one update statement without if and else.
Create procedure Test
(
@col1 int,
@col2 int
)
AS
If @col1 =1
Update TestTable
set col3 = 100
else if @col1 is anything else but 1 then
Update TestTable
set @col3 = @col1
This:
update dbo.TestTable set
col3 = case when @col1 = 1 then 100 else @col1 end
where <some condition> -- without a where clause every row in the table will be updated
May 10, 2016 at 2:29 pm
anjaliagarwal5 (5/10/2016)
Hi All,I am passing a parameter as @col1. I need to set col3 to 100 if @col1 =1 else col3 will be any value that is passed in. Is there any way, I can remove the else statement and write one update statement without if and else.
Create procedure Test
(
@col1 int,
@col2 int
)
AS
If @col1 =1
Update TestTable
set col3 = 100
else if @col1 is anything else but 1 then
Update TestTable
set @col3 = @col1
update TestTable
Set col3 = iif(@Col1 = 1, 100, @Col1)
--edit: the WHERE clause warning above is important!
--edit #2: changed 300 to 100, thanks Lynn.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 10, 2016 at 2:31 pm
Phil Parkin (5/10/2016)
anjaliagarwal5 (5/10/2016)
Hi All,I am passing a parameter as @col1. I need to set col3 to 100 if @col1 =1 else col3 will be any value that is passed in. Is there any way, I can remove the else statement and write one update statement without if and else.
Create procedure Test
(
@col1 int,
@col2 int
)
AS
If @col1 =1
Update TestTable
set col3 = 100
else if @col1 is anything else but 1 then
Update TestTable
set @col3 = @col1
update TestTableSet col3 = iif(@Col1 = 1, 300, @Col1)
--edit: the WHERE clause warning above is important!
That should be 100, not 300.
May 10, 2016 at 2:33 pm
You could re-write that as one UPDATE with a CASE statement.
CREATE PROCEDURE Test
(
@col1 INT,
@col2 INT
)
AS
UPDATE TestTable
SET col3 =
CASE
WHEN@col1 = 1 THEN 100
ELSE @col1
END
Edit: golly you guys are fast.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply