在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