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

2022-11-12 09:53:52
内容摘要
这篇文章主要为大家详细介绍了SQL Server 在sql查询中使用表变量,具有一定的参考价值,可以用来参考一下。 对此感兴趣的朋友,看看idc笔记做的技术笔记! 代码如下: USE [DAF_
文章正文

这篇文章主要为大家详细介绍了SQL Server 在sql查询中使用表变量,具有一定的参考价值,可以用来参考一下。

对此感兴趣的朋友,看看idc笔记做的技术笔记!

代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
<code>
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
</code>

注:关于SQL Server 在sql查询中使用表变量的内容就先介绍到这里,更多相关文章的可以留意

代码注释

作者:喵哥笔记

IDC笔记

学的不仅是技术,更是梦想!