用友_存货代码自动改为助计码的存储过程

zh3305

• [size=1em]SET ANSI_NULLS ON
  
• [size=1em]GO
  
• [size=1em]SET QUOTED_IDENTIFIER ON
  
• [size=1em]GO
  
• [size=1em]CREATE TRIGGER [TRIGGER 存货代码自动生成
  
• [size=1em]   ON dbo.Inventory
  
• [size=1em] FOR INSERT, UPDATE
  
• [size=1em]AS
  
• [size=1em]BEGIN
  
• [size=1em]    SET NOCOUNT ON;
  
• [size=1em]IF  not EXISTS (SELECT * FROM SYSOBJECTS  WHERE NAME ='f_GetPy' AND Xtype='FN')
  
• [size=1em]begin
  
• [size=1em]exec ('create function f_GetPy(@str nvarchar(4000)) returns nvarchar(4000)
  
• [size=1em]as
  
• [size=1em]begin
  
• [size=1em]declare @strlen int,@re nvarchar(4000)
  
• [size=1em]declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
  
• [size=1em]insert into @t(chr,letter)
  
• [size=1em] select ''吖'',''A'' union all select ''八'',''B'' union all
  
• [size=1em] select ''嚓'',''C'' union all select ''咑'',''D'' union all
  
• [size=1em] select ''妸'',''E'' union all select ''发'',''F'' union all
  
• [size=1em] select ''旮'',''G'' union all select ''铪'',''H'' union all
  
• [size=1em] select ''丌'',''J'' union all select ''咔'',''K'' union all
  
• [size=1em] select ''垃'',''L'' union all select ''嘸'',''M'' union all
  
• [size=1em] select ''拏'',''N'' union all select ''噢'',''O'' union all
  
• [size=1em] select ''妑'',''P'' union all select ''七'',''Q'' union all
  
• [size=1em] select ''呥'',''R'' union all select ''仨'',''S'' union all
  
• [size=1em] select ''他'',''T'' union all select ''屲'',''W'' union all
  
• [size=1em] select ''夕'',''X'' union all select ''丫'',''Y'' union all
  
• [size=1em] select ''帀'',''Z''
  
• [size=1em] select @strlen=len(@str),@re=''''
  
• [size=1em] while @strlen>0
  
• [size=1em] begin
  
• [size=1em]   select top 1 @re=letter+@re,@strlen=@strlen-1
  
• [size=1em]     from @t a where chr<=substring(@str,@strlen,1)
  
• [size=1em]     order by chr desc
  
• [size=1em]   if @@rowcount=0
  
• [size=1em]     select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
  
• [size=1em] end
  
• [size=1em] return(@re)
  
• [size=1em]end')
  
• [size=1em]end
  
• [size=1em]declare @cInvCode  nvarchar(1000)  ,@cInvName  nvarchar(1000)  
  
• [size=1em]declare In_Prdetail_Cursor cursor for Select cInvCode ,cInvName  from INSERTED
  
• [size=1em]open In_Prdetail_Cursor
  
• [size=1em]Fetch next from In_Prdetail_Cursor into @cInvCode,@cInvName
  
• [size=1em]While @@fetch_status=0  
  
• [size=1em]begin
  
• [size=1em]update dbo.Inventory set cInvAddCode= dbo.f_GetPy(@cInvName) where cInvCode= @cInvCode
  
• 
  
• [size=1em]Fetch next from In_Prdetail_Cursor into @cInvCode,@cInvName
  
• [size=1em]end
  
• [size=1em]close In_Prdetail_Cursor
  
• [size=1em]deallocate In_Prdetail_Cursor
  
• 
  
• [size=1em]END
  
• [size=1em]GO
  
  

zh3305

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [TRIGGER 存货代码自动生成]
   ON dbo.Inventory
FOR INSERT, UPDATE
AS
BEGIN
        SET NOCOUNT ON;
IF  not EXISTS (SELECT * FROM SYSOBJECTS  WHERE NAME ='f_GetPy' AND Xtype='FN')
begin
exec ('create function f_GetPy(@str nvarchar(4000)) returns nvarchar(4000)
as
begin
declare @strlen int,@re nvarchar(4000)
declare @t table(chr nchar(1) collate Chinese_PRC_CI_AS,letter nchar(1))
insert into @t(chr,letter)
  select ''吖'',''A'' union all select ''八'',''B'' union all
  select ''嚓'',''C'' union all select ''咑'',''D'' union all
  select ''妸'',''E'' union all select ''发'',''F'' union all
  select ''旮'',''G'' union all select ''铪'',''H'' union all
  select ''丌'',''J'' union all select ''咔'',''K'' union all
  select ''垃'',''L'' union all select ''嘸'',''M'' union all
  select ''拏'',''N'' union all select ''噢'',''O'' union all
  select ''妑'',''P'' union all select ''七'',''Q'' union all
  select ''呥'',''R'' union all select ''仨'',''S'' union all
  select ''他'',''T'' union all select ''屲'',''W'' union all
  select ''夕'',''X'' union all select ''丫'',''Y'' union all
  select ''帀'',''Z''
  select @strlen=len(@str),@re=''''
  while @strlen>0
  begin
    select top 1 @re=letter+@re,@strlen=@strlen-1
      from @t a where chr<=substring(@str,@strlen,1)
      order by chr desc
    if @@rowcount=0
      select @re=substring(@str,@strlen,1)+@re,@strlen=@strlen-1
  end
  return(@re)
end')
end
declare @cInvCode  nvarchar(1000)  ,@cInvName  nvarchar(1000)   
declare In_Prdetail_Cursor cursor for Select cInvCode ,cInvName  from INSERTED
open In_Prdetail_Cursor
Fetch next from In_Prdetail_Cursor into @cInvCode,@cInvName
While @@fetch_status=0  
begin
update dbo.Inventory set cInvAddCode= dbo.f_GetPy(@cInvName) where cInvCode= @cInvCode

Fetch next from In_Prdetail_Cursor into @cInvCode,@cInvName
end
close In_Prdetail_Cursor
deallocate In_Prdetail_Cursor

END
GO

johnny117

不错 我喜欢

zbmrz

学习学习谢楼主

lzlcxl

先回复，再下载

lzlcxl

金币一下全下载没了

mv1199

可以为指定范围的科目自动加上祖籍吗吗

小野

怎么样弄。。。。。。啊

ireton1980

这个有用，下来看看

casbo68

这个如果能用就相当强大了，鸡肋的用友啊

haoych

您要查看本帖隐藏内

zhuyha00

不错，支持一下

浙江小新

好东西，自然要大家分享

szsos

http://www.tubr.com/club

永恒的爱恋

我来看看什么了