[cisco-voip] Fix for CSCti68188 dtmfname is null

Chase Mergenthal chase at nothingbutnetllc.com
Tue Oct 19 10:41:47 EDT 2010


I just tossed together a script to add dtmf name info for this bug. I'm 
sure it could have been done better by someone that has more experience 
in mssql than me; but here it is; use at your own risk!!
-Chase

declare @FirstName nvarchar(32), @LastName nvarchar(32), 
@DtmfNameFirstLast nvarchar(32), @DtmfNameLastFirst nvarchar(32), at Alias 
nvarchar(64)
declare @firstlen int, @lastlen int, @i int

set @i = 1;

select * from GlobalSubscriber

select @Alias = min(Alias) from GlobalSubscriber


while @Alias is not null begin
    set @i = 1;
    select @FirstName = FirstName from GlobalSubscriber where Alias = @Alias
    select @LastName = LastName from GlobalSubscriber where Alias = @Alias
    select @DtmfNameFirstLast = DtmfNameFirstLast from GlobalSubscriber 
where Alias = @Alias
    select @DtmfNameLastFirst = DtmfNameLastFirst from GlobalSubscriber 
where Alias = @Alias

------------------------------------------------

--print ' FirstName: ' + cast(@FirstName as varchar) + ' LastName: ' + 
cast(@LastName as varchar) + ' DtmfNameFirstLast:' + 
cast(@DtmfNameFirstLast as varchar) + ' DtmfNameLastFirst:' + 
cast(@DtmfNameLastFirst as varchar)

if @DtmfNameFirstLast is NULL or @DtmfNameLastFirst is NULL
begin
    print @Alias
set @firstlen = LEN(@firstname);
set @lastlen = LEN(@lastname);

IF SUBSTRING(@firstname, at i,1)  = 'a' OR SUBSTRING(@firstname, at i,1)  = 
'A' set @DtmfNameFirstLast = '2';
IF SUBSTRING(@firstname, at i,1)  = 'b' OR SUBSTRING(@firstname, at i,1)  = 
'B' set @DtmfNameFirstLast = '2';
IF SUBSTRING(@firstname, at i,1)  = 'c' OR SUBSTRING(@firstname, at i,1)  = 
'C' set @DtmfNameFirstLast = '2';
IF SUBSTRING(@firstname, at i,1)  = 'd' OR SUBSTRING(@firstname, at i,1)  = 
'D' set @DtmfNameFirstLast = '3';
IF SUBSTRING(@firstname, at i,1)  = 'e' OR SUBSTRING(@firstname, at i,1)  = 
'E' set @DtmfNameFirstLast = '3';
IF SUBSTRING(@firstname, at i,1)  = 'f' OR SUBSTRING(@firstname, at i,1)  = 
'F' set @DtmfNameFirstLast = '3';
IF SUBSTRING(@firstname, at i,1)  = 'g' OR SUBSTRING(@firstname, at i,1)  = 
'G' set @DtmfNameFirstLast = '4';
IF SUBSTRING(@firstname, at i,1)  = 'h' OR SUBSTRING(@firstname, at i,1)  = 
'H' set @DtmfNameFirstLast = '4';
IF SUBSTRING(@firstname, at i,1)  = 'i' OR SUBSTRING(@firstname, at i,1)  = 
'I' set @DtmfNameFirstLast = '4';
IF SUBSTRING(@firstname, at i,1)  = 'j' OR SUBSTRING(@firstname, at i,1)  = 
'J' set @DtmfNameFirstLast = '5';
IF SUBSTRING(@firstname, at i,1)  = 'k' OR SUBSTRING(@firstname, at i,1)  = 
'K' set @DtmfNameFirstLast = '5';
IF SUBSTRING(@firstname, at i,1)  = 'l' OR SUBSTRING(@firstname, at i,1)  = 
'L' set @DtmfNameFirstLast = '5';
IF SUBSTRING(@firstname, at i,1)  = 'm' OR SUBSTRING(@firstname, at i,1)  = 
'M' set @DtmfNameFirstLast = '6';
IF SUBSTRING(@firstname, at i,1)  = 'n' OR SUBSTRING(@firstname, at i,1)  = 
'N' set @DtmfNameFirstLast = '6';
IF SUBSTRING(@firstname, at i,1)  = 'o' OR SUBSTRING(@firstname, at i,1)  = 
'O' set @DtmfNameFirstLast = '6';
IF SUBSTRING(@firstname, at i,1)  = 'p' OR SUBSTRING(@firstname, at i,1)  = 
'P' set @DtmfNameFirstLast = '7';
IF SUBSTRING(@firstname, at i,1)  = 'q' OR SUBSTRING(@firstname, at i,1)  = 
'Q' set @DtmfNameFirstLast = '7';
IF SUBSTRING(@firstname, at i,1)  = 'r' OR SUBSTRING(@firstname, at i,1)  = 
'R' set @DtmfNameFirstLast = '7';
IF SUBSTRING(@firstname, at i,1)  = 's' OR SUBSTRING(@firstname, at i,1)  = 
'S' set @DtmfNameFirstLast = '7';
IF SUBSTRING(@firstname, at i,1)  = 't' OR SUBSTRING(@firstname, at i,1)  = 
'T' set @DtmfNameFirstLast = '8';
IF SUBSTRING(@firstname, at i,1)  = 'u' OR SUBSTRING(@firstname, at i,1)  = 
'U' set @DtmfNameFirstLast = '8';
IF SUBSTRING(@firstname, at i,1)  = 'v' OR SUBSTRING(@firstname, at i,1)  = 
'V' set @DtmfNameFirstLast = '8';
IF SUBSTRING(@firstname, at i,1)  = 'w' OR SUBSTRING(@firstname, at i,1)  = 
'W' set @DtmfNameFirstLast = '9';
IF SUBSTRING(@firstname, at i,1)  = 'x' OR SUBSTRING(@firstname, at i,1)  = 
'X' set @DtmfNameFirstLast = '9';
IF SUBSTRING(@firstname, at i,1)  = 'y' OR SUBSTRING(@firstname, at i,1)  = 
'Y' set @DtmfNameFirstLast = '9';
IF SUBSTRING(@firstname, at i,1)  = 'z' OR SUBSTRING(@firstname, at i,1)  = 
'Z' set @DtmfNameFirstLast = '9';

while @i<@firstlen
begin
set @i = @i + 1;

IF SUBSTRING(@firstname, at i,1)  = 'a' OR SUBSTRING(@firstname, at i,1)  = 
'A' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '2';
IF SUBSTRING(@firstname, at i,1)  = 'b' OR SUBSTRING(@firstname, at i,1)  = 
'B' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '2';
IF SUBSTRING(@firstname, at i,1)  = 'c' OR SUBSTRING(@firstname, at i,1)  = 
'C' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '2';
IF SUBSTRING(@firstname, at i,1)  = 'd' OR SUBSTRING(@firstname, at i,1)  = 
'D' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '3';
IF SUBSTRING(@firstname, at i,1)  = 'e' OR SUBSTRING(@firstname, at i,1)  = 
'E' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '3';
IF SUBSTRING(@firstname, at i,1)  = 'f' OR SUBSTRING(@firstname, at i,1)  = 
'F' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '3';
IF SUBSTRING(@firstname, at i,1)  = 'g' OR SUBSTRING(@firstname, at i,1)  = 
'G' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '4';
IF SUBSTRING(@firstname, at i,1)  = 'h' OR SUBSTRING(@firstname, at i,1)  = 
'H' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '4';
IF SUBSTRING(@firstname, at i,1)  = 'i' OR SUBSTRING(@firstname, at i,1)  = 
'I' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '4';
IF SUBSTRING(@firstname, at i,1)  = 'j' OR SUBSTRING(@firstname, at i,1)  = 
'J' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '5';
IF SUBSTRING(@firstname, at i,1)  = 'k' OR SUBSTRING(@firstname, at i,1)  = 
'K' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '5';
IF SUBSTRING(@firstname, at i,1)  = 'l' OR SUBSTRING(@firstname, at i,1)  = 
'L' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '5';
IF SUBSTRING(@firstname, at i,1)  = 'm' OR SUBSTRING(@firstname, at i,1)  = 
'M' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '6';
IF SUBSTRING(@firstname, at i,1)  = 'n' OR SUBSTRING(@firstname, at i,1)  = 
'N' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '6';
IF SUBSTRING(@firstname, at i,1)  = 'o' OR SUBSTRING(@firstname, at i,1)  = 
'O' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '6';
IF SUBSTRING(@firstname, at i,1)  = 'p' OR SUBSTRING(@firstname, at i,1)  = 
'P' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '7';
IF SUBSTRING(@firstname, at i,1)  = 'q' OR SUBSTRING(@firstname, at i,1)  = 
'Q' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '7';
IF SUBSTRING(@firstname, at i,1)  = 'r' OR SUBSTRING(@firstname, at i,1)  = 
'R' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '7';
IF SUBSTRING(@firstname, at i,1)  = 's' OR SUBSTRING(@firstname, at i,1)  = 
'S' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '7';
IF SUBSTRING(@firstname, at i,1)  = 't' OR SUBSTRING(@firstname, at i,1)  = 
'T' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '8';
IF SUBSTRING(@firstname, at i,1)  = 'u' OR SUBSTRING(@firstname, at i,1)  = 
'U' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '8';
IF SUBSTRING(@firstname, at i,1)  = 'v' OR SUBSTRING(@firstname, at i,1)  = 
'V' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '8';
IF SUBSTRING(@firstname, at i,1)  = 'w' OR SUBSTRING(@firstname, at i,1)  = 
'W' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '9';
IF SUBSTRING(@firstname, at i,1)  = 'x' OR SUBSTRING(@firstname, at i,1)  = 
'X' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '9';
IF SUBSTRING(@firstname, at i,1)  = 'y' OR SUBSTRING(@firstname, at i,1)  = 
'Y' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '9';
IF SUBSTRING(@firstname, at i,1)  = 'z' OR SUBSTRING(@firstname, at i,1)  = 
'Z' set @DtmfNameFirstLast = @DtmfNameFirstLast +  '9';
end

set @i = 1;

while @i<=@lastlen
begin
IF SUBSTRING(@lastname, at i,1)  = 'a' OR SUBSTRING(@lastname, at i,1)  = 'A' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '2';
IF SUBSTRING(@lastname, at i,1)  = 'b' OR SUBSTRING(@lastname, at i,1)  = 'B' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '2';
IF SUBSTRING(@lastname, at i,1)  = 'c' OR SUBSTRING(@lastname, at i,1)  = 'C' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '2';
IF SUBSTRING(@lastname, at i,1)  = 'd' OR SUBSTRING(@lastname, at i,1)  = 'D' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '3';
IF SUBSTRING(@lastname, at i,1)  = 'e' OR SUBSTRING(@lastname, at i,1)  = 'E' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '3';
IF SUBSTRING(@lastname, at i,1)  = 'f' OR SUBSTRING(@lastname, at i,1)  = 'F' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '3';
IF SUBSTRING(@lastname, at i,1)  = 'g' OR SUBSTRING(@lastname, at i,1)  = 'G' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '4';
IF SUBSTRING(@lastname, at i,1)  = 'h' OR SUBSTRING(@lastname, at i,1)  = 'H' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '4';
IF SUBSTRING(@lastname, at i,1)  = 'i' OR SUBSTRING(@lastname, at i,1)  = 'I' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '4';
IF SUBSTRING(@lastname, at i,1)  = 'j' OR SUBSTRING(@lastname, at i,1)  = 'J' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '5';
IF SUBSTRING(@lastname, at i,1)  = 'k' OR SUBSTRING(@lastname, at i,1)  = 'K' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '5';
IF SUBSTRING(@lastname, at i,1)  = 'l' OR SUBSTRING(@lastname, at i,1)  = 'L' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '5';
IF SUBSTRING(@lastname, at i,1)  = 'm' OR SUBSTRING(@lastname, at i,1)  = 'M' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '6';
IF SUBSTRING(@lastname, at i,1)  = 'n' OR SUBSTRING(@lastname, at i,1)  = 'N' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '6';
IF SUBSTRING(@lastname, at i,1)  = 'o' OR SUBSTRING(@lastname, at i,1)  = 'O' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '6';
IF SUBSTRING(@lastname, at i,1)  = 'p' OR SUBSTRING(@lastname, at i,1)  = 'P' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '7';
IF SUBSTRING(@lastname, at i,1)  = 'q' OR SUBSTRING(@lastname, at i,1)  = 'Q' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '7';
IF SUBSTRING(@lastname, at i,1)  = 'r' OR SUBSTRING(@lastname, at i,1)  = 'R' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '7';
IF SUBSTRING(@lastname, at i,1)  = 's' OR SUBSTRING(@lastname, at i,1)  = 'S' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '7';
IF SUBSTRING(@lastname, at i,1)  = 't' OR SUBSTRING(@lastname, at i,1)  = 'T' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '8';
IF SUBSTRING(@lastname, at i,1)  = 'u' OR SUBSTRING(@lastname, at i,1)  = 'U' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '8';
IF SUBSTRING(@lastname, at i,1)  = 'v' OR SUBSTRING(@lastname, at i,1)  = 'V' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '8';
IF SUBSTRING(@lastname, at i,1)  = 'w' OR SUBSTRING(@lastname, at i,1)  = 'W' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '9';
IF SUBSTRING(@lastname, at i,1)  = 'x' OR SUBSTRING(@lastname, at i,1)  = 'X' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '9';
IF SUBSTRING(@lastname, at i,1)  = 'y' OR SUBSTRING(@lastname, at i,1)  = 'Y' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '9';
IF SUBSTRING(@lastname, at i,1)  = 'z' OR SUBSTRING(@lastname, at i,1)  = 'Z' 
set @DtmfNameFirstLast = @DtmfNameFirstLast +  '9';
set @i = @i + 1;
end
---------------------------
set @i = 1;
IF SUBSTRING(@lastname, at i,1)  = 'a' OR SUBSTRING(@lastname, at i,1)  = 'A' 
set @DtmfNameLastFirst = '2';
IF SUBSTRING(@lastname, at i,1)  = 'b' OR SUBSTRING(@lastname, at i,1)  = 'B' 
set @DtmfNameLastFirst = '2';
IF SUBSTRING(@lastname, at i,1)  = 'c' OR SUBSTRING(@lastname, at i,1)  = 'C' 
set @DtmfNameLastFirst = '2';
IF SUBSTRING(@lastname, at i,1)  = 'd' OR SUBSTRING(@lastname, at i,1)  = 'D' 
set @DtmfNameLastFirst = '3';
IF SUBSTRING(@lastname, at i,1)  = 'e' OR SUBSTRING(@lastname, at i,1)  = 'E' 
set @DtmfNameLastFirst = '3';
IF SUBSTRING(@lastname, at i,1)  = 'f' OR SUBSTRING(@lastname, at i,1)  = 'F' 
set @DtmfNameLastFirst = '3';
IF SUBSTRING(@lastname, at i,1)  = 'g' OR SUBSTRING(@lastname, at i,1)  = 'G' 
set @DtmfNameLastFirst = '4';
IF SUBSTRING(@lastname, at i,1)  = 'h' OR SUBSTRING(@lastname, at i,1)  = 'H' 
set @DtmfNameLastFirst = '4';
IF SUBSTRING(@lastname, at i,1)  = 'i' OR SUBSTRING(@lastname, at i,1)  = 'I' 
set @DtmfNameLastFirst = '4';
IF SUBSTRING(@lastname, at i,1)  = 'j' OR SUBSTRING(@lastname, at i,1)  = 'J' 
set @DtmfNameLastFirst = '5';
IF SUBSTRING(@lastname, at i,1)  = 'k' OR SUBSTRING(@lastname, at i,1)  = 'K' 
set @DtmfNameLastFirst = '5';
IF SUBSTRING(@lastname, at i,1)  = 'l' OR SUBSTRING(@lastname, at i,1)  = 'L' 
set @DtmfNameLastFirst = '5';
IF SUBSTRING(@lastname, at i,1)  = 'm' OR SUBSTRING(@lastname, at i,1)  = 'M' 
set @DtmfNameLastFirst = '6';
IF SUBSTRING(@lastname, at i,1)  = 'n' OR SUBSTRING(@lastname, at i,1)  = 'N' 
set @DtmfNameLastFirst = '6';
IF SUBSTRING(@lastname, at i,1)  = 'o' OR SUBSTRING(@lastname, at i,1)  = 'O' 
set @DtmfNameLastFirst = '6';
IF SUBSTRING(@lastname, at i,1)  = 'p' OR SUBSTRING(@lastname, at i,1)  = 'P' 
set @DtmfNameLastFirst = '7';
IF SUBSTRING(@lastname, at i,1)  = 'q' OR SUBSTRING(@lastname, at i,1)  = 'Q' 
set @DtmfNameLastFirst = '7';
IF SUBSTRING(@lastname, at i,1)  = 'r' OR SUBSTRING(@lastname, at i,1)  = 'R' 
set @DtmfNameLastFirst = '7';
IF SUBSTRING(@lastname, at i,1)  = 's' OR SUBSTRING(@lastname, at i,1)  = 'S' 
set @DtmfNameLastFirst = '7';
IF SUBSTRING(@lastname, at i,1)  = 't' OR SUBSTRING(@lastname, at i,1)  = 'T' 
set @DtmfNameLastFirst = '8';
IF SUBSTRING(@lastname, at i,1)  = 'u' OR SUBSTRING(@lastname, at i,1)  = 'U' 
set @DtmfNameLastFirst = '8';
IF SUBSTRING(@lastname, at i,1)  = 'v' OR SUBSTRING(@lastname, at i,1)  = 'V' 
set @DtmfNameLastFirst = '8';
IF SUBSTRING(@lastname, at i,1)  = 'w' OR SUBSTRING(@lastname, at i,1)  = 'W' 
set @DtmfNameLastFirst = '9';
IF SUBSTRING(@lastname, at i,1)  = 'x' OR SUBSTRING(@lastname, at i,1)  = 'X' 
set @DtmfNameLastFirst = '9';
IF SUBSTRING(@lastname, at i,1)  = 'y' OR SUBSTRING(@lastname, at i,1)  = 'Y' 
set @DtmfNameLastFirst = '9';
IF SUBSTRING(@lastname, at i,1)  = 'z' OR SUBSTRING(@lastname, at i,1)  = 'Z' 
set @DtmfNameLastFirst = '9';

while @i<@lastlen
begin
set @i = @i + 1;

IF SUBSTRING(@lastname, at i,1)  = 'a' OR SUBSTRING(@lastname, at i,1)  = 'A' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '2';
IF SUBSTRING(@lastname, at i,1)  = 'b' OR SUBSTRING(@lastname, at i,1)  = 'B' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '2';
IF SUBSTRING(@lastname, at i,1)  = 'c' OR SUBSTRING(@lastname, at i,1)  = 'C' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '2';
IF SUBSTRING(@lastname, at i,1)  = 'd' OR SUBSTRING(@lastname, at i,1)  = 'D' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '3';
IF SUBSTRING(@lastname, at i,1)  = 'e' OR SUBSTRING(@lastname, at i,1)  = 'E' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '3';
IF SUBSTRING(@lastname, at i,1)  = 'f' OR SUBSTRING(@lastname, at i,1)  = 'F' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '3';
IF SUBSTRING(@lastname, at i,1)  = 'g' OR SUBSTRING(@lastname, at i,1)  = 'G' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '4';
IF SUBSTRING(@lastname, at i,1)  = 'h' OR SUBSTRING(@lastname, at i,1)  = 'H' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '4';
IF SUBSTRING(@lastname, at i,1)  = 'i' OR SUBSTRING(@lastname, at i,1)  = 'I' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '4';
IF SUBSTRING(@lastname, at i,1)  = 'j' OR SUBSTRING(@lastname, at i,1)  = 'J' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '5';
IF SUBSTRING(@lastname, at i,1)  = 'k' OR SUBSTRING(@lastname, at i,1)  = 'K' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '5';
IF SUBSTRING(@lastname, at i,1)  = 'l' OR SUBSTRING(@lastname, at i,1)  = 'L' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '5';
IF SUBSTRING(@lastname, at i,1)  = 'm' OR SUBSTRING(@lastname, at i,1)  = 'M' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '6';
IF SUBSTRING(@lastname, at i,1)  = 'n' OR SUBSTRING(@lastname, at i,1)  = 'N' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '6';
IF SUBSTRING(@lastname, at i,1)  = 'o' OR SUBSTRING(@lastname, at i,1)  = 'O' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '6';
IF SUBSTRING(@lastname, at i,1)  = 'p' OR SUBSTRING(@lastname, at i,1)  = 'P' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '7';
IF SUBSTRING(@lastname, at i,1)  = 'q' OR SUBSTRING(@lastname, at i,1)  = 'Q' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '7';
IF SUBSTRING(@lastname, at i,1)  = 'r' OR SUBSTRING(@lastname, at i,1)  = 'R' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '7';
IF SUBSTRING(@lastname, at i,1)  = 's' OR SUBSTRING(@lastname, at i,1)  = 'S' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '7';
IF SUBSTRING(@lastname, at i,1)  = 't' OR SUBSTRING(@lastname, at i,1)  = 'T' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '8';
IF SUBSTRING(@lastname, at i,1)  = 'u' OR SUBSTRING(@lastname, at i,1)  = 'U' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '8';
IF SUBSTRING(@lastname, at i,1)  = 'v' OR SUBSTRING(@lastname, at i,1)  = 'V' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '8';
IF SUBSTRING(@lastname, at i,1)  = 'w' OR SUBSTRING(@lastname, at i,1)  = 'W' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '9';
IF SUBSTRING(@lastname, at i,1)  = 'x' OR SUBSTRING(@lastname, at i,1)  = 'X' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '9';
IF SUBSTRING(@lastname, at i,1)  = 'y' OR SUBSTRING(@lastname, at i,1)  = 'Y' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '9';
IF SUBSTRING(@lastname, at i,1)  = 'z' OR SUBSTRING(@lastname, at i,1)  = 'Z' 
set @DtmfNameLastFirst = @DtmfNameLastFirst +  '9';
end

set @i = 1;

while @i<=@firstlen
begin
IF SUBSTRING(@firstname, at i,1)  = 'a' OR SUBSTRING(@firstname, at i,1)  = 
'A' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '2';
IF SUBSTRING(@firstname, at i,1)  = 'b' OR SUBSTRING(@firstname, at i,1)  = 
'B' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '2';
IF SUBSTRING(@firstname, at i,1)  = 'c' OR SUBSTRING(@firstname, at i,1)  = 
'C' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '2';
IF SUBSTRING(@firstname, at i,1)  = 'd' OR SUBSTRING(@firstname, at i,1)  = 
'D' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '3';
IF SUBSTRING(@firstname, at i,1)  = 'e' OR SUBSTRING(@firstname, at i,1)  = 
'E' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '3';
IF SUBSTRING(@firstname, at i,1)  = 'f' OR SUBSTRING(@firstname, at i,1)  = 
'F' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '3';
IF SUBSTRING(@firstname, at i,1)  = 'g' OR SUBSTRING(@firstname, at i,1)  = 
'G' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '4';
IF SUBSTRING(@firstname, at i,1)  = 'h' OR SUBSTRING(@firstname, at i,1)  = 
'H' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '4';
IF SUBSTRING(@firstname, at i,1)  = 'i' OR SUBSTRING(@firstname, at i,1)  = 
'I' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '4';
IF SUBSTRING(@firstname, at i,1)  = 'j' OR SUBSTRING(@firstname, at i,1)  = 
'J' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '5';
IF SUBSTRING(@firstname, at i,1)  = 'k' OR SUBSTRING(@firstname, at i,1)  = 
'K' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '5';
IF SUBSTRING(@firstname, at i,1)  = 'l' OR SUBSTRING(@firstname, at i,1)  = 
'L' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '5';
IF SUBSTRING(@firstname, at i,1)  = 'm' OR SUBSTRING(@firstname, at i,1)  = 
'M' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '6';
IF SUBSTRING(@firstname, at i,1)  = 'n' OR SUBSTRING(@firstname, at i,1)  = 
'N' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '6';
IF SUBSTRING(@firstname, at i,1)  = 'o' OR SUBSTRING(@firstname, at i,1)  = 
'O' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '6';
IF SUBSTRING(@firstname, at i,1)  = 'p' OR SUBSTRING(@firstname, at i,1)  = 
'P' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '7';
IF SUBSTRING(@firstname, at i,1)  = 'q' OR SUBSTRING(@firstname, at i,1)  = 
'Q' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '7';
IF SUBSTRING(@firstname, at i,1)  = 'r' OR SUBSTRING(@firstname, at i,1)  = 
'R' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '7';
IF SUBSTRING(@firstname, at i,1)  = 's' OR SUBSTRING(@firstname, at i,1)  = 
'S' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '7';
IF SUBSTRING(@firstname, at i,1)  = 't' OR SUBSTRING(@firstname, at i,1)  = 
'T' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '8';
IF SUBSTRING(@firstname, at i,1)  = 'u' OR SUBSTRING(@firstname, at i,1)  = 
'U' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '8';
IF SUBSTRING(@firstname, at i,1)  = 'v' OR SUBSTRING(@firstname, at i,1)  = 
'V' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '8';
IF SUBSTRING(@firstname, at i,1)  = 'w' OR SUBSTRING(@firstname, at i,1)  = 
'W' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '9';
IF SUBSTRING(@firstname, at i,1)  = 'x' OR SUBSTRING(@firstname, at i,1)  = 
'X' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '9';
IF SUBSTRING(@firstname, at i,1)  = 'y' OR SUBSTRING(@firstname, at i,1)  = 
'Y' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '9';
IF SUBSTRING(@firstname, at i,1)  = 'z' OR SUBSTRING(@firstname, at i,1)  = 
'Z' set @DtmfNameLastFirst = @DtmfNameLastFirst +  '9';
set @i = @i + 1;
end
---------------------------

---------------------------

  --if @Alias='SmallJ' begin
  update GlobalSubscriber set DtmfNameLastFirst=@DtmfNameLastFirst where 
DtmfNameLastFirst is NULL AND  Alias = @Alias
  update GlobalSubscriber set DtmfNameFirstLast=@DtmfNameFirstLast where 
DtmfNameFirstLast is NULL AND  Alias = @Alias
  print 'UPDATE FirstName: ' + cast(@FirstName as varchar) + ' LastName: 
' + cast(@LastName as varchar) + ' DtmfNameFirstLast:' + 
cast(@DtmfNameFirstLast as varchar) + ' DtmfNameLastFirst:' + 
cast(@DtmfNameLastFirst as varchar)
  --end

end

select @Alias = min(Alias) from GlobalSubscriber where Alias > @Alias

end

select * from GlobalSubscriber


More information about the cisco-voip mailing list