拼音码查询 存储过程+触发器

 

 

关于T-SQL语句建 拼音码,汉字码对照表在

http://download.youkuaiyun.com/source/326464 下载

-- 存储过程  将汉字转化成拼音输出
create   proc  Hz2Py
     @hz   varchar ( 200 ),
     @py   varchar ( 200 ) output
AS
declare   @i   int
declare   @chr   varchar ( 2 )
declare   @pyc   varchar ( 1 )
set   @i = 1
while  ( @i <= len ( @hz ))  begin
   set   @chr = substring ( @hz , @i , 1 )
   set   @pyc = ( select  py  from  pycode  where  hz = @chr )
   if ( @pyc   is   null )
     set   @py = @py + @chr
   else
     set   @py = @py + @pyc
   Set   @i = @i + 1
end

-- 触发器 当插入,更新时 更改拼音码
create   trigger  Namepy  on  bank  for   insert , update
AS
declare   @name   varchar ( 100 ), @npy   varchar ( 100 ), @cid   char ( 10 )

if ( update (customername))  begin
   set   @npy = ''
   select   @name = customername, @cid = cardid  from  inserted
   exec  Hz2Py  @name , @npy  output

   update  bank  set  namepy = @npy   where  cardid = @cid

end
GO