IF-ELSE - 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

SQL Server 2000 Forums

SQL Server Development (2000)

IF-ELSE

Author

Topic

anuradhay
Starting Member

41 Posts

Posted - 2005-03-10 : 03:47:48

I have 2 doubts in if..else
1) i have a if block like the following one in my procedure
if (select account_no from static..account_nos_alpha (NOLOCK)
where branch_code = '714'
and ccy_code = 'usd'
and alpha_code = 'BRUSSELS') != @o_account
begin
--select @found = @found | 1024
select 'found'--,@found
end

If the select statement is not returning any rows(no records for that condition) what will happen to the flow
will it come to the if block or else block??

2) In the following piece of code if i add ltrim rtrim to @o_account then it is going to else block. otherwise it is going to if block.
but the variable is anyway a varchar one. so what is the point in adding ltrim(rtrim())?

Want to know why it is behaving like this??

declare @o_account varchar(34)
select @o_account = '0800591780'
select @o_account,ltrim(rtrim(@o_account)),datalength(@o_account),datalength(ltrim(rtrim(@o_account)))

if (select account_no from static..account_nos_alpha (NOLOCK)
where branch_code = '714'
and ccy_code = 'usd'
and alpha_code = 'BRUSSELS') != @o_account
begin
--select @found = @found | 1024
select 'found'--,@found
end

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2005-03-10 : 04:02:08

1) if the select statement does not return anything , it will go to else block.

He is a fool for five minutes who asks ,
but who does not ask remains a fool for life!

http://www.sqldude.4t.com
http://www.sqljunkies.com/weblog/sqldude

anuradhay
Starting Member

41 Posts

Posted - 2005-03-10 : 04:24:56

1) is ok.
2) do u have any idea about point no 2?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-10 : 04:38:51

What is the datatype of Account_No?

Madhivanan

Failing to plan is Planning to fail

anuradhay
Starting Member

41 Posts

Posted - 2005-03-10 : 05:00:35

actually both are of different datatype, Account_no is char(10) and @o_account is varchar(34). I thought of converting the @o_account to char. If i add convert in the if statement it is working. But if i remove the convert and change the datatype to char in declare statement, it is not working..

If i keep the variable as varchar(34), and add ltrim(rtrim()) then it is working. but i can't understand why should i add trim functions to varchar variable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-10 : 05:48:26

The Char datatype will preserve the trailing spaces.
eg. If you use a column of Char type with size 20 and assign value having 15 characters then the field actaully have 20 characters with 5 trailing spaces

Madhivanan

Failing to plan is Planning to fail

anuradhay
Starting Member

41 Posts

Posted - 2005-03-10 : 05:55:44

But that variable is of varchar type no. so it shouldn't be a prob

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-10 : 06:04:56

Can you post the full code you used?

Madhivanan

Failing to plan is Planning to fail

anuradhay
Starting Member

41 Posts

Posted - 2005-03-10 : 06:28:20

/*
begin tran
exec fco_exist_test '714','USD','CITIBEBX'
rollback
*/
alter procedure fco_exist_test
@branch_code char(3),
@ccy char(3),
@ipcode varchar(34)
as
begin
declare @o_account varchar(34),
@alpha_code char(12),
@found smallint

SELECT @found = 1

select @alpha_code = alpha_code from static..bic_codes (NOLOCK)
where bic = @ipcode

select @o_account = std_o_a_1
from static..ccy_std_accs (NOLOCK)
where branch_code = @branch_code
and ccy_code = @ccy

if (select account_no from static..account_nos_alpha (NOLOCK)
where branch_code = @branch_code
and ccy_code = @ccy
and alpha_code = @alpha_code) != @o_account
begin
select @found = @found | 1024
select 'found',@found
end

select @found
return

End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources