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 not working

Author  Topic 

gongxia649
So Suave

344 Posts

Posted - 2006-09-20 : 17:03:06
declare @table table(ad_num varchar(20), ad_str1 varchar(20))

insert @table
select '20', '20 apple avenue' union all
select '20', 'apple avenue'

select * from @table

if (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))

print 'address starts with a number'
else
print 'address starts with a letter'



i want to write a code that prints a msg if the string starts with a number else print another msg.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-20 : 17:16:25
Looks like you're confusing IF with CASE. Check these out:

declare @table table(ad_num varchar(20), ad_str1 varchar(20))

insert @table
select '20', '20 apple avenue' union all
select '20', 'apple avenue'


select * from @table

select ad_str1
,case
when substring(ad_str1, 1, 1) like '[0-9]'
then 'address starts with a number'
else 'address starts with a letter'
end
from @table

declare @ad_str1 varchar(20)
select top 1 @ad_str1 = ad_str1 from @table

if substring(@ad_str1,1,1) like '[0-9]'
print @ad_str1 + ': address starts with a number'
else
print @ad_str1 + ': address starts with a letter'



Be One with the Optimizer
TG
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-20 : 17:20:57
Not much point in using SubString, in fact it's less optimal than:

select ad_str1
,case
when ad_str1 like '[0-9]%'
then 'address starts with a number'
else 'address starts with a letter'
end
from @table


This is semantically equivalent and it avoids the non-sargable SubString function, on a large table with an indexed column it should be dramatically faster.
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-20 : 21:03:54
TG, why doesn't run without storing the field in a variable?
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-09-20 : 22:12:38
quote:
Originally posted by gongxia649

TG, why doesn't run without storing the field in a variable?



IF expects to evaluate a boolean expression (true or false). Your "exression":
if (select * from @table where substring(ad_str1, 1, 1) like '[0-9]'))
does not result in a boolean expression but rather a result set. Also, you've got an extra parenthises at the end.

Be One with the Optimizer
TG
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-21 : 16:42:15

truncate table dbo.TempAddressParsingTable
insert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)
select '1', null, '102 OLD COUNTY ROAD' union all
select '21', null, '1 UNTY ROAD' union all
select '31', null, 'route 1' union all
select '14', null, 'pob 23' union all
select '15', null, 'database apt3' union all
select '2','30', 'SHAW ROAD apt #9'

-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'
-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'

select * from dbo.TempAddressParsingTable




declare @ad_str1 varchar(20)
select @ad_str1 = ad_str1 from dbo.TempAddressParsingTable

if substring(@ad_str1,1,1) like '[a-z]'
exec proc1
else
exec proc2

The code should execute proc1 if the field ad_str1 first letter is a letter, otherwise exec proc2.
The code runs fine, but it's not doing what the proc are suppose to do. But if i run the SP alone, it will do what is suppose to do. Why is this happening? How can i fix it?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 16:49:37
quote:
Originally posted by gongxia649

Why is this happening? How can i fix it?
You could start learning basic algorithms.
select @currentvoter = min(id_voter),
@maxvoter = max(id_voter)
from tempaddressparsingtable

while @currentvoter <= @maxvoter
begin
select @ad = ad_str
from tempaddressparsingtable
where id_voter = @currentvoter

if @ad like '[a-z]%'
exec proc1 @ad
else
exec proc2 @ad

select @currentvoter = min(id_voter)
from tempaddressparsingtable
where id_voter > @currentvoter
end

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-22 : 08:21:57
truncate table dbo.TempAddressParsingTable
insert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)
select '1', null, '102 OLD COUNTY ROAD' union all
select '21', null, '1 UNTY ROAD' union all
select '31', null, 'route 1' union all
select '14', null, 'pob 23' union all
select '15', null, 'database apt3' union all
select '2','30', 'SHAW ROAD apt #9'

-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'
-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'

select * from dbo.TempAddressParsingTable



declare @currentvoter varchar(20)
declare @maxvoter varchar(20)
declare @ad varchar(20)


select @currentvoter = min(id_voter) from tempaddressparsingtable
select @maxvoter = max(id_voter)from tempaddressparsingtable

while @currentvoter <= @maxvoter
begin
select @ad = substring(ad_str1, 1,1)
from dbo.tempAddressParsingtable
where id_voter = @currentvoter

if substring(@ad, 1,1) like '[a-z]%'

exec usp_Trio_AddressParsing1
else
exec usp_NDS_AddressParsing1

select @currentvoter = min(id_voter)
from dbo.tempaddressparsingtable
where id_voter > @currentvoter
end

I don't know what i'm doing wrong. When i run the code, the SP are not doing the job. But if I exec them alone, they work fine.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 08:37:20
Why, Oh Why, are you changing the code and logic in the suggestions?
And you are totally clueless why the code doesn't work after your changes? I am a peaceful person, but you are starting to make me dislike you.

Why do you use SUBSTRING twice? Do you not trust us who tries to help you?
If the stored procedures are not "doing the job", have you thought of the possibility that my suggestion is right and your stored procedures has errors when executed?

Always, when debugging, replace code to calling other stored procedures with a PRINT statement, like the one below. This way you will be convinced that my suggestion is good enough and the appropriate stored procedure is called.

Also in my first suggestion, I called the different stored procedures with a parameter, the address. Now when you have deleted them, how will the two procedures know what to work with?
SET NOCOUNT ON

DECLARE @TAPT TABLE (id_voter VARCHAR(2), ad_num VARCHAR(2), ad_str1 VARCHAR(20))

INSERT @TAPT
SELECT '1', null, '102 OLD COUNTY ROAD' UNION ALL
SELECT '21', null, '1 UNTY ROAD' UNION ALL
SELECT '31', null, 'route 1' UNION ALL
SELECT '14', null, 'pob 23' UNION ALL
SELECT '15', null, 'database apt3' UNION ALL
SELECT '2','30', 'SHAW ROAD apt #9'

SELECT * FROM @TAPT

DECLARE @CurrentVoter VARCHAR(2),
@MaxVoter VARCHAR(2),
@ad VARCHAR(20)

SELECT @CurrentVoter = MIN(id_voter),
@MaxVoter = MAX(id_voter)
FROM @TAPT

WHILE @CurrentVoter <= @MaxVoter
BEGIN
SELECT @ad = ad_str1
FROM @TAPT
WHERE id_voter = @CurrentVoter

IF @ad LIKE '[a-z]%'
--EXEC usp_Trio_AddressParsing1
PRINT 'Address ' + @ad + ' does start with a character!'
ELSE
--EXEC usp_NDS_AddressParsing1
PRINT 'Address ' + @ad + ' does not start with a character!'

SELECT @CurrentVoter = MIN(id_voter)
FROM @TAPT
WHERE id_voter > @CurrentVoter
END

Peter Larsson
Helsingborg, Sweden

EDIT: Spelling errors
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 08:46:59
Output from the code above gives following
Address 102 OLD COUNTY ROAD does not start with a character!
Address pob 23 does start with a character!
Address database apt3 does start with a character!
Address SHAW ROAD apt #9 does start with a character!
Address 1 UNTY ROAD does not start with a character!
Address route 1 does start with a character!
As you can the, the result is correct. Where is the error, you think?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-22 : 08:57:05
peso, i didnt see the parameter next to the SP. I have to do some changes now. But my SP doesnt receive any parameter.


Also in my first suggestion, I called the different stored procedures with a parameter, the address. Now when you have deleted them, how will the two procedures know what to work with?

I thought the SP know how to work with them after the IF statement has ran.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:06:32
quote:
Originally posted by gongxia649

I thought the SP know how to work with them after the IF statement has ran.
I am aware you thought so and didn't know, that's why I provided a suggestion for a correct execution to you.

No, the stored procedure does not know which address to work with, if you do not provide the address to the stored procedure.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-22 : 09:27:23
Peso,
"No, the stored procedure does not know which address to work with, if you do not provide the address to the stored procedure."

Does that mean I have to go to my SP and change the whole thing?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:27:44
How would I know? I don't have a crystal ball.

It all depends on what your stored procedures does!

If the case is that your stored procedure usp_Trio_AddressParsing1 already knows how to deal with addresses that starts with characters (all addresses in the table, not just one row) then you might add a WHERE clause in the SP, as

where address like '[a-z]%'


If the case is that your stored procedure usp_NDS_AddressParsing1 already knows how to deal with addresses (all addresses in the table, not just one row) that start with a non-character, then you might add a WHERE cluse in the SP, as

where address like '[^a-z]%'

or

where address like '[0-9]%'


Then all you need is two calls to either SP, and this topic is obselete to start with.


We can't tell you what to do. We give valuable suggestions. Our suggestions are based on the information you provide us.
Not once in this entire topic have you told us what the two stores procedures do. So we think you know what you are doing with the loop and all, as in the original posting.

Maybe there were no need for a loop at all!?
It all depends on what your stored procedures does!


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-22 : 09:47:48
IF 1<>1 THEN PRINT 'NOT WORKING' ELSE PRINT 'WORKING'



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-22 : 09:48:24
usp_Trio_AddressParsing1 = parse the address that no number in front
usp_NDS_AddressParsing1 = parse the address that has numbers in front

i added "where address like '[0-9]%'" and "where address like '[a-z]%'" to all the rows in my sp. But still doesnt' do that job.

If i run the SP alone. e.g. i run only usp_Trio_AddressParsing1, it will parse the address
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:50:32
the address

or

the addresses


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-22 : 09:55:29
peso, addresses

you know what, i think its working.
I dont even neeed to use IF cause the SP knows when to parse the addresses with number in front and without a number in front.


so, i just go


truncate table dbo.TempAddressParsingTable
insert dbo.TempAddressParsingTable (id_voter,ad_num,ad_str1)
select '1', null, '102 OLD COUNTY ROAD' union all
select '21', null, '1 UNTY ROAD' union all
select '31', null, 'route 1' union all
select '14', null, 'pob 23' union all
select '15', null, 'database apt3' union all
select '2','30', 'SHAW ROAD apt #9'

-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[a-z]'
-- select * from dbo.TempAddressParsingTable where substring(ad_str1,1,1) like '[0-9]'

select * from dbo.TempAddressParsingTable
usp_Trio_AddressParsing1
usp_NDS_AddressParsing1


and bang it works.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 09:57:41
I know.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

gongxia649
So Suave

344 Posts

Posted - 2006-09-22 : 09:59:14
sorry agian to wasted your time.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 10:00:29
Well, it might not be a wate of time, if you have improved you T-SQL skills and learned something from this mistakes and never do the same mistake again.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -