在sql查询中使用表变量
时间:2024-01-23 10:25:59
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
标签:sql,表变量
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
基于Python绘制3D立体爱心图案的示例详解
2021-04-03 18:05:09
![](https://img.aspxhome.com/file/2023/3/68263_0s.png)
Python类型转换的魔术方法详解
2022-04-16 12:49:06
详细HTTP协议的前世今生
2022-05-16 20:33:26
![](https://img.aspxhome.com/file/2023/4/132404_0s.jpg)
十个惊艳的Pythonic单行代码
2022-07-29 06:34:20
vue-form表单验证是否为空值的实例详解
2024-04-30 10:40:03
用javascript实现gb2312转utf-8的脚本
2024-04-16 08:58:42
Python实现一个发送程序和接收程序
2023-06-18 09:04:38
![](https://img.aspxhome.com/file/2023/6/75376_0s.png)
django rest framework vue 实现用户登录详解
2022-04-15 00:53:52
![](https://img.aspxhome.com/file/2023/1/101171_0s.png)
python difflib模块示例讲解
2022-01-21 04:35:57
![](https://img.aspxhome.com/file/2023/5/90795_0s.png)
对于Python异常处理慎用“except:pass”建议
2023-01-30 02:30:44
python绘制立方体的方法
2022-09-26 10:34:14
![](https://img.aspxhome.com/file/2023/7/109407_0s.jpg)
SQL Server 2008 安装SQLDMO.dll的方法
2024-01-12 18:13:15
一文搞懂Go语言标准库strconv
2024-04-23 09:41:45
Git 教程之基本操作详解
2023-08-04 08:04:20
JS中的forEach、$.each、map方法推荐
2024-04-29 13:19:59
win32com操作word之Application&Documents接口学习
2021-03-01 13:30:34
详解Python3定时器任务代码
2023-10-15 14:50:26
移除Selenium中window.navigator.webdriver值
2023-07-11 16:21:36
![](https://img.aspxhome.com/file/2023/5/66895_0s.png)
Python解释器及PyCharm工具安装过程
2021-02-23 08:21:51
![](https://img.aspxhome.com/file/2023/8/118508_0s.jpg)
基于Python编写一个中秋节嫦娥投食小游戏
2022-12-27 10:46:34
![](https://img.aspxhome.com/file/2023/7/134457_0s.gif)