在sql查询中使用表变量

USE   [ DAF_DB ]
GO
/* ***** Object:  StoredProcedure [dbo].[PROG_WORKTASK_List]    Script Date: 06/14/2010 21:14:43 ***** */
SET  ANSI_NULLS  ON
GO
SET  QUOTED_IDENTIFIER  ON
GO

-- [PROG_WORKTASK_List] 62,0,'','',0,0,'',''

--  =============================================
--  Author:        guo.lin
--  Create date: 2010-03-10
--  Description:    获取工作任务单列表，根据相关条件
--  =============================================
ALTER   PROCEDURE   [ dbo ] . [ PROG_WORKTASK_List ]  
     @userID   varchar ( 10 ),     -- -当前用户
     @status   int ,     -- -状态
     @Leader   varchar ( 30 ),
     @Facilitators   varchar ( 30 ),
     @Level   int  ,
     @Priority   int ,
     @CloseDT   varchar ( 30 ),
     @CreatedBy   varchar ( 30 )

AS
BEGIN
     declare   @result   table (
        taskid  int ,
        TaskName  nvarchar ( 100 ),
        TaskTopic  nvarchar ( 100 ),
         Level   varchar ( 10 ),
        Priority  varchar ( 10 ),
        Status  varchar ( 10 ),
        Leader  varchar ( 100 ),
        strLeader  nvarchar ( 500 ),
        Facilitators  varchar ( 100 ),
        cycletime  varchar ( 10 ),
        CloseDT  datetime ,
        Createddt  datetime ,
        CreatedBy  varchar ( 30 )
    )
    
     insert   into   @result   select  taskid,TaskName,TaskTopic, Level ,Priority,Status,Leader,strLeader,Facilitators,cycletime,CloseDT,Createddt,CreatedBy 
         from  Work_TaskProcess  where  createdby = @CreatedBy   or  leader  like   ' %| ' + @userid + ' |% '   or  Facilitators  like   ' %| ' + @userid + ' |% '

     if  ( @status   <> 0 )
         delete   @result   where  status <> @status
     if  ( @Leader <> '' )
         delete   @result   where  leader  not   like   ' %| ' + @Leader + ' |% '
--     if (@CreatedBy<>'')
--         delete @result where CreatedBy<>@CreatedBy
     if  ( @Level   <>   0 ) 
         delete   @result   where   level <> @level
     if  ( @Priority   <>   0 )
         delete   @result   where  Priority <> @Priority
     if  ( @CloseDT <> '' )
         delete   @result   where  CloseDT <> @CloseDT

     update   @result   set  status = b.itemtext  from   @result  a,DAF_Item b  where  a.Status = b.itemvalue  and  b.itemtype = ' worktaskStatus '
     update   @result   set   Level = b.itemtext  from   @result  a,DAF_Item b  where  a. Level = b.itemvalue  and  b.itemtype = ' worktaskLevel '
     update   @result   set  Priority = b.itemtext  from   @result  a,DAF_Item b  where  a.Priority = b.itemvalue  and  b.itemtype = ' worktaskPriority '
     update   @result   set  createdby = b.username  from   @result  a, DAF_useraccount b  where  a.createdBy = b.userid
     update   @result   set  cycletime = cycletime + ' 天 '

    
     select  taskid,TaskName,TaskTopic, Level ,Priority,Status,strLeader,Facilitators,cycletime,CloseDT,Createddt,CreatedBy  from   @result   order   by  Createddt  desc

END

 

本文转自火地晋博客园博客，原文链接：http://www.cnblogs.com/yelaiju/archive/2010/06/14/1758394.html，如需转载请自行联系原作者