Copy codeThe Code is as follows:
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 ,'',''
-- ===================================================== ======
-- Author: guo. lin
-- Create date: 2010-03-10
-- Description: gets the list of job orders based on relevant conditions.
-- ===================================================== ======
Alter procedure [dbo]. [PROG_WORKTASK_List]
@ UserID varchar (10), --- current user
@ Status int, --- status
@ 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 + 'day'
Select taskid, TaskName, TaskTopic, Level, Priority, Status, strLeader, Facilitators, cycletime, CloseDT, Createddt, CreatedBy from @ result order by Createddt desc
END