在sql查询中使用表变量分享

–[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