在sql查询中使用表变量

145 阅读 0 评论 96 点赞

我是靠谱客的博主犹豫热狗，最近开发中收集的这篇文章主要介绍在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 