if else statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if else statement

Author

Topic

ashley.s
Starting Member

9 Posts

Posted - 2012-04-23 : 08:18:18

Dear All, please check the code below. I am using sql server 2008.

declare @test bit;
select @test = transbit from [Cage 1];
If @test=0 begin update [Cage 1] set Transbit=1 where SerialNumber='AG000001-2' end;
Else if @test=1 Begin Update [Cage 1] set Transbit=0 where SerialNumber='AG000001-2' End;

The above code is working only if @test=false. When @test is set to true, it is supposed to set back to false. But it is not. Can you please help me?

Thanks

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-23 : 08:32:37

Please post the DDL and some sample data otherwise how are we supposed to know what transbit returns from Cage1 to @test.
Without sample data its not possible to test the query even.
Please do the above mentioned stuff and help us in helping you.

From what I understood I tested it with some data. It seems to work perfectly for me.


--Creating Table

Create Table Cage1
(SerialNumber varchar(20),
 Transbit bit )
 

--Inserting Data
 
Insert Into Cage1 Values('AG000001-2', 0)


--Testing Script for false

declare @test bit;
select @test = transbit from [Cage1];
If @test=0 begin update [Cage1] set Transbit=1 where SerialNumber='AG000001-2' end;
Else if @test=1 Begin Update [Cage1] set Transbit=0 where SerialNumber='AG000001-2' End;


--Verifying Result

Select * From Cage1

--Testing Script for true

declare @test bit;
select @test = transbit from [Cage1];
If @test=0 begin update [Cage1] set Transbit=1 where SerialNumber='AG000001-2' end;
Else if @test=1 Begin Update [Cage1] set Transbit=0 where SerialNumber='AG000001-2' End;


--Verifying Result

Select * From Cage1

If it doesn't for you then maybe we are looking at the problem from different perspectives. So, to make things better you need to be more specific ie: post DDL and sample data.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-23 : 08:43:19

May be you need a where clause in the select statement as well?

declare @test bit;
select @test = transbit from [Cage 1] where SerialNumber = 'AG000001-2';
If @test=0 begin update [Cage 1] set Transbit=1 where SerialNumber='AG000001-2' end;
Else if @test=1 Begin Update [Cage 1] set Transbit=0 where SerialNumber='AG000001-2' End;

If the only thing you are trying to do is to toggle transbit, then you can do it with less code like this:

UPDATE [Cage 1] SET 
	transbit = CASE WHEN transbit = 1 THEN 0 ELSE 1 END
WHERE SerialNumber = 'AG000001-2'

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-23 : 08:54:09

Yup!!...Sunita is right.
When you have multiple rows in your table, the following code:

declare @test bit;
select @test = transbit from [Cage1];
Select @test

Only returns '0'.
So in order to make it work you have to use a 'Where' clause with the Select Statement as well.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

ashley.s
Starting Member

9 Posts

Posted - 2012-04-23 : 10:39:29

thank you so much guys. :):):):)

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-24 : 00:39:20

You're welcome.
And thank you Sunita. Its always good to learn from Pros like you.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources