SQL Server 在sql查询中使用表变量

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

代码如下:

 
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,'','' 

-- ============================================= 
-- 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,CreatedBy from @result order by Createddt desc 

END 

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!