Nested IF Statements

Here is what i am trying to do

select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End

end

from dbo.test1Users

When i do that i get this error
Incorrect syntax near the keyword 'from'.

Thank you for your help

You need to use CASE instead of IF. Maybe something like:

Code Snippet

declare @.testUsers table
( id integer,
firstName varchar(25),
lastName varchar(25)
)
insert into @.testUsers
select 1, 'Fred', 'Rubble' union all
select 2, 'Mike', 'Flintstone' union all
select 3, 'Betty', 'Kong' union all
select 4, 'Mike', 'Kong'
--select * from @.testUsers

select id,
case when substring(firstName, 1, 4) = 'Mike'
then replace(firstName, 'Mike', 'MikeTest')
else firstName
end as firstName,
case when substring(lastName, 1, 4) = 'Kong'
then replace(lastName, 'Kong', 'KongTest')
else lastName
end as lastName
from @.testUsers

/*
id firstName lastName
- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty KongTest
4 MikeTest KongTest
*/

|||

Thank you for your replay

Actually I already thought about case statements but it really won’t work and here is why:

I need to have one check in top for example

If(firstName, 1, 4) = 'Mike')

Begin
replace(firstName, 'Mike', 'MikeTest')

If(LatsName, 1, 4) = 'Kong')

Begin

replace(firstName, 'Kong', 'KongTest')

End

End

The reason why ! you might have a guy his first name is “George” and his last name is “Kong”. So George’s Last name should not be changed to kongtest. Case statement won’t work because it will execute each case for example: the output from your case statements was:

id firstName lastName
- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty KongTest

4 MikeTest KongTest

Betty’s last name should not be changed to KongTest

Thanks in advance for your Input

|||

That corrected case statement looks something like this:

Code Snippet

declare @.testUsers table
( id integer,
firstName varchar(25),
lastName varchar(25)
)
insert into @.testUsers
select 1, 'Fred', 'Rubble' union all
select 2, 'Mike', 'Flintstone' union all
select 3, 'Betty', 'Kong' union all
select 4, 'Mike', 'Kong'
--select * from @.testUsers

select id,
case when substring(firstName, 1, 4) = 'Mike'
then replace(firstName, 'Mike', 'MikeTest')
else firstName
end as firstName,
case when substring(firstName, 1, 4) = 'Mike'
and substring(lastName, 1, 4) = 'Kong'
then replace(lastName, 'Kong', 'KongTest')
else lastName
end as lastName
from @.testUsers

/*
id firstName lastName
-- - --
1 Fred Rubble
2 MikeTest Flintstone
3 Betty Kong
4 MikeTest KongTest
*/

|||

Yes this might work only for simple cases, but what I have is First Name, Last Name, Street Address, City,state,, County, Country, zip Code and more info....... Can you see my point?

Thanks

|||

What was stated was a specific case; if you will give what your aim is, then I might be able to help you. Yes, I see your rhetorical point.

|||

Thank you Kent.

Ok I am tying to convert a query that was written in access to Sql stored procedure or view..

Here is the part that I need to convert:

SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,

IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',

IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',

IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',

IIf(Mid([proj_name],1,9)='9900-2787','Sales',

IIf(Mid([proj_name],1,9)='9910-2799','Sales',

IIf(Mid([proj_name],1,9)='9920-2791','Sales',

IIf(Mid([proj_name],1,9)='9026-3342','Perspective',

IIf(Mid([proj_name],1,4)='9995','Perspective',

IIf(Mid([proj_name],1,4)='9016','MONITOR Internal',

IIf(Mid([proj_name],1,4)='9011','MONITOR Internal',

IIf(Mid([proj_name],1,4)='9010','MONITOR Internal',

IIf(Mid([proj_name],1,4)>'99',IIf(Mid([proj_name],1,3)='999','MONITOR Internal','Admin'),'Client')

)

)

)

)

)

)

)

)

)

)

) AS timeType, Sum([2007_hours].Hours) AS SumOfHours

from................

Your input will be appreciated

Thank you