付费服务：两个SQL Server下的存储过程要移植到DB2上

netawu

现有两个SQL Server下的存储过程要移植到IBM DB2 企业版V7.2上
不知道那位愿意帮忙，可以付费，可以通过QQ（25687）或MSN（storageonline@hotmail.com）联系我。

现公布其中一个存储过程的全部代码，请各位参考。另一个存储过程，与此类似。因为用的是DB2 V7.2，所以应该可以直接用SQL语句写DB2的存储过程。


sql server存储过程代码开始
============================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[search_Article]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[search_Article]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--入口参数 查询的条件 pageNum,showNum,s_name,s_value,s_orderby,s_startdate,s_enddate,siteid
--输出参数 count,查询倒的结果的条数

CREATE PROCEDURE [dbo].[search_Article]
     
      @pageNum           int,
      @showNum           int,
      @s_name           VARCHAR(256),
      @s_value          VARCHAR(256),
      @s_orderby        VARCHAR(256),
      @s_startdate      VARCHAR(20),
      @s_enddate        VARCHAR(20),
      @siteid           int,
      @count            int output
      
AS      
    set nocount on
--内部变量
      declare @ARTICLEID        int
      declare @TITLE            VARCHAR(256)
      declare @STARTDATE        datetime
      declare @ENDDATE          datetime
      declare @ISAUDIT          VARCHAR(1)
      declare @ISGENERATE       VARCHAR(1)
      declare @STATE            VARCHAR(1)
      declare @ISOUTLINK        VARCHAR(1)
      declare @OUTLINKURL       VARCHAR(256)
      declare @ARTICLEURL       VARCHAR(256)
      declare @TYPE             VARCHAR(1)
      declare @MEDIAID          int
      declare @TEMPLETID        int
      declare @OPERATOR         int
      declare @mediaName        VARCHAR(256)
      declare @templetName      VARCHAR(256)
      declare @operatorName     VARCHAR(256)
      
      declare @i                int
      
--根据传入的查询条件，组成查询语句 定义游标
      IF @s_startdate <>;''
      BEGIN
            
            IF @s_name = 'title'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and title like @s_value and modifydate>;@s_startdate and modifydate<@s_enddate order by modifydate desc
                  select @count = (select count(*)   FROM article_tbl where siteid=@siteid and type in(0,2) and title like @s_value and modifydate>;@s_startdate and modifydate<@s_enddate)
                  END
            ELSE IF @s_name = 'author'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and author like @s_value and  modifydate>;@s_startdate and modifydate<@s_enddate order by modifydate desc
                  select @count = (select count(*) from article_tbl where siteid=@siteid and type in(0,2) and author like @s_value and  modifydate>;@s_startdate and modifydate<@s_enddate )
                  END
            ELSE IF @s_name ='description'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and description like @s_value and  modifydate>;@s_startdate and modifydate<@s_enddate order by modifydate desc
                  select @count = (select count(*) from article_tbl where siteid=@siteid and type in(0,2) and description like @s_value and  modifydate>;@s_startdate and modifydate<@s_enddate )
                  END
            ELSE IF @s_name = 'articleurl'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and articleurl like @s_value and  modifydate>;@s_startdate and modifydate<@s_enddate order by modifydate desc
                  select @count = (select count(*) from  article_tbl where siteid=@siteid and type in(0,2) and articleurl like @s_value and  modifydate>;@s_startdate and modifydate<@s_enddate )
                  END
            ELSE IF @s_name = 'isaudit'
                  BEGIN      
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and isaudit=@s_value and  modifydate>;@s_startdate and modifydate<@s_enddate order by modifydate desc
                  select @count = (select count(*) from article_tbl where siteid=@siteid and type in(0,2) and isaudit=@s_value and  modifydate>;@s_startdate and modifydate<@s_enddate)
                  END
            ELSE IF @s_name = 'isgenerate'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and isgenerate=@s_value and  modifydate>;@s_startdate and modifydate<@s_enddate order by modifydate desc
                  select @count = (select count(*) FROM article_tbl where siteid=@siteid and type in(0,2) and isgenerate=@s_value and modifydate>;@s_startdate and modifydate<@s_enddate )
                  END
            ELSE IF @s_name = 'state'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and state=@s_value and  modifydate>;@s_startdate and modifydate<@s_enddate order by modifydate desc
                  select @count = (select count(*) FROM article_tbl where siteid=@siteid and type in(0,2) and state=@s_value and  modifydate>;@s_startdate and modifydate<@s_enddate )
                  END
            ELSE IF @s_name = 'mediaName'
                 BEGIN
                 DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,article_tbl.state,isoutlink,outlinkurl,articleurl,type,article_tbl.mediaid,templetid,article_tbl.operator FROM article_tbl,media_tbl O where article_tbl.mediaid=O.mediaid and article_tbl.siteid=@siteid  and article_tbl.type='0' and O.name like @s_value and article_tbl.modifydate>;@s_startdate and article_tbl.modifydate<@s_enddate order by article_tbl.modifydate desc
                 select @count = (select count(*) FROM article_tbl,media_tbl O  where article_tbl.mediaid=O.mediaid and article_tbl.siteid=@siteid and type='0' and O.name like @s_value and article_tbl.modifydate>;@s_startdate and article_tbl.modifydate<@s_enddate )
                  END
            ELSE IF @s_name = 'templetName'
                 BEGIN
                 DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,article_tbl.state,isoutlink,outlinkurl,articleurl,article_tbl.type,article_tbl.mediaid,article_tbl.templetid,article_tbl.operator FROM article_tbl,templet_tbl O where article_tbl.templetid=O.templetid and article_tbl.siteid=@siteid and article_tbl.type='0' and O.name like @s_value and article_tbl.modifydate>;@s_startdate and article_tbl.modifydate<@s_enddate order by article_tbl.modifydate desc
                 select @count = (select count(*) FROM article_tbl,templet_tbl O where article_tbl.templetid=O.templetid and article_tbl.siteid=@siteid and article_tbl.type='0' and O.name like @s_value and article_tbl.modifydate>;@s_startdate and article_tbl.modifydate<@s_enddate )
                  END
            ELSE
                 BEGIN
                 DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and modifydate>;@s_startdate and modifydate<@s_enddate order by modifydate desc
                 select @count = (select count(*) FROM article_tbl where siteid=@siteid and type in(0,2) and modifydate>;@s_startdate and modifydate<@s_enddate )
                 END
      END
      ELSE
     
      BEGIN
            
            IF @s_name = 'title'
                  BEGIN

                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and title like @s_value order by modifydate desc
                  select @count = (select count(*)   FROM article_tbl where siteid=@siteid and type in(0,2) and title like @s_value )
                  END
            ELSE IF @s_name = 'author'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and author like @s_value order by modifydate desc
                  select @count = (select count(*)  FROM article_tbl where siteid=@siteid and type in(0,2) and author like @s_value )
                  END
            ELSE IF @s_name ='description'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and description like @s_value  order by modifydate desc
                  select @count = (select count(*) FROM article_tbl where siteid=@siteid and type in(0,2) and description like @s_value  )
                  END
            ELSE IF @s_name = 'articleurl'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and articleurl like @s_value order by modifydate desc
                  select @count = (select count(*)  FROM article_tbl where siteid=@siteid and type in(0,2) and articleurl like @s_value )
                  END
            ELSE IF @s_name = 'isaudit'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and isaudit=@s_value order by modifydate desc
                  select @count = (select count(*) FROM article_tbl where siteid=@siteid and type in(0,2) and isaudit=@s_value )
                  END
            ELSE IF @s_name = 'isgenerate'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and isgenerate=@s_value  order by modifydate desc
                  select @count = (select count(*)  FROM article_tbl where siteid=@siteid and type in(0,2) and isgenerate=@s_value  )
                  END
            ELSE IF @s_name = 'state'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) and state=@s_value order by modifydate desc
                  select @count = (select count(*) FROM article_tbl where siteid=@siteid and type in(0,2) and state=@s_value )
                  END
            ELSE IF @s_name = 'mediaName'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,article_tbl.state,isoutlink,outlinkurl,articleurl,article_tbl.type,article_tbl.mediaid,templetid,article_tbl.operator FROM article_tbl,media_tbl O where article_tbl.mediaid=O.mediaid and article_tbl.siteid=@siteid and article_tbl.type='0' and O.name like @s_value order by  article_tbl.modifydate desc
                  select @count = (select count(*) FROM article_tbl,media_tbl O where article_tbl.mediaid=O.mediaid and article_tbl.siteid=@siteid and type='0' and O.name like @s_value )
                  END
            ELSE IF @s_name = 'templetName'
                  BEGIN
                  DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,article_tbl.state,isoutlink,outlinkurl,articleurl,article_tbl.type,mediaid,article_tbl.templetid,article_tbl.operator FROM article_tbl,templet_tbl O where article_tbl.templetid=O.templetid and article_tbl.siteid=@siteid and article_tbl.type='0' and O.name like @s_value  order by article_tbl.modifydate desc
                  select @count = (select count(*) FROM article_tbl,templet_tbl O where article_tbl.templetid=O.templetid and article_tbl.siteid=@siteid and article_tbl.type='0' and O.name like @s_value  )
                  END
            ELSE
                 BEGIN
                 DECLARE a_cur CURSOR FOR select articleid,title,CONVERT(CHAR,startdate,120) as startdate,CONVERT(CHAR,enddate,120) as enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaid,templetid,operator FROM article_tbl where siteid=@siteid and type in(0,2) order by modifydate desc
                 select @count = (select count(*)  FROM article_tbl where siteid=@siteid and type in(0,2) )
                  END
      END
    -- Delete from article_log            
--删除临时表article_log
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Article_log]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Article_log]
--创建临时表article_log，用来放置本次查询的结果集
CREATE TABLE [dbo].[Article_log] (
        [id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
        [articleid] [numeric](18, 0) NOT NULL ,
        [templetName] [char] (256) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [title] [char] (256) COLLATE Chinese_PRC_CI_AS NULL ,
        [type] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
        [articleurl] [char] (256) COLLATE Chinese_PRC_CI_AS NULL ,
        [isaudit] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
        [isgenerate] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [isoutlink] [char] (1) COLLATE Chinese_PRC_CI_AS NULL ,
        [outlinkurl] [char] (256) COLLATE Chinese_PRC_CI_AS NULL ,
        [mediaName] [char] (256) COLLATE Chinese_PRC_CI_AS NULL ,
        [startdate] [datetime] NOT NULL ,
        [enddate] [datetime] NOT NULL ,
        [state] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [operatorName] [char] (256) COLLATE Chinese_PRC_CI_AS NOT NULL
)  

--打开游标，并把查询倒的信息插入临时表
      OPEN a_cur
      FETCH NEXT FROM a_cur INTO @articleid,@title,@startdate,@enddate,@isaudit,@isgenerate,@state,@isoutlink,@outlinkurl,@articleurl,@type,@mediaid,@templetid,@operator
     
      SELECT @i=1

      WHILE (@@FETCH_STATUS = 0) AND @i <= @count
        BEGIN
                IF @i>;@pageNum*@showNum
                                BREAK
                     IF @i>;(@pageNum-1)*@showNum AND @i<=@pageNum*@showNum
                        BEGIN  
                                select @mediaName = ''
                                select @templetName = ''
                                select @operatorName = ''
                                
                                if @mediaid >;0
                                    select @mediaName = (select name from media_tbl where mediaid = @mediaid)
                                if @templetid >;0
                                    select @templetName = (select name from templet_tbl where templetid = @templetid)
                                if @operator>;0
                                    select @operatorName = (select manager from manager_tbl where managerid=@operator)
                                if @mediaName = null     
                                    select @mediaName = 'deleted'
                                if @templetName = null     
                                    select @templetName = 'deleted'
                                if @operatorName = null     
                                    select @operatorName = 'deleted'                                   
                                   
                                insert into article_log (articleid,title,startdate,enddate,isaudit,isgenerate,state,isoutlink,outlinkurl,articleurl,type,mediaName,templetName,operatorName) values (@articleid,@title,@startdate,@enddate,@isaudit,@isgenerate,@state,@isoutlink,@outlinkurl,@articleurl,@type,@mediaName,@templetName,@operatorName)
                        END
                        --insert into test_tbl (count) values (999)
                        FETCH NEXT FROM a_cur INTO @articleid,@title,@startdate,@enddate,@isaudit,
                                @isgenerate,@state,@isoutlink,@outlinkurl,@articleurl,@type,@mediaid,@templetid,@operator
                        SELECT @i= @i +1

                END
             
CLOSE a_cur
DEALLOCATE a_cur



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO