代碼 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

?

轉載于:https://www.cnblogs.com/yelaiju/archive/2010/06/14/1758394.html

總結

以上是生活随笔為你收集整理的在sql查询中使用表变量的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。