charindex

[deepa]

I have following code in stored procedure. It was working before.

declare @mail_cd varchar(3),
@pointtype_id int, @mailcd varchar(3),@j int,@i int

select @mail_cd = 'WT'
select @i = charindex('WT',@mail_cd,1)

select @mailcd =''

select @i
if @i = 1
begin
select @mailcd = substring(rtrim(@mail_cd),1,2)
end
else
begin
select @mailcd = substring(rtrim(@mail_cd),1,1)
end

select @mail_cd,@mailcd

select @j = charindex('LF',@mail_cd,1)

if @j = 1
begin
select @mailcd = substring(rtrim(@mail_cd),1,2)
end
else
begin
select @mailcd = substring(rtrim(@mail_cd),1,1)
end

select @pointtype_id = code_id
from Code c,type ct
where c.type_id = ct.type_id
and ct.[name] = 'pointtype'
and code = @mailcd

select @mailcd,@pointtype_id


I get result

---- -----------
W 178

(1 row(s) affected)

instead of

---- -----------
WT 278

(1 row(s) affected)

Any idea what is wrong? Thanks in advance

[skhanal]

If your query

select @pointtype_id = code_id
from Code c,type ct
where c.type_id = ct.type_id
and ct.[name] = 'pointtype'
and code = @mailcd

returns more than one row then the variable will have the data from last row.

[deepa]

There is only one row. It is unique For ct.[name] = 'pointtype' and code = @mailcd.

Thanks for reply.

[mikr0s]

.............
select @mail_cd,@mailcd --@mail_cd = 'WT', @mailcd = 'WT'

select @j = charindex('LF',@mail_cd,1)

--at this point @j = 0, since 'LF' doesnt appear/exist in @mail_cd = 'WT'

if @j = 1 --so this is skipped, since @j=0
begin
select @mailcd = substring(rtrim(@mail_cd),1,2)
end
else
begin --and this step is valid/executed
select @mailcd = substring(rtrim(@mail_cd),1,1) --@mailcd becomes 'W' 
end
--at this point @mailcd = 'W'
..............

--HTH--