在sql查询中使用表变量
程序员文章站
2022-05-19 16:18:45
...
在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
推荐阅读
-
sql在where查询语句中使用变量的实例讲解
-
有关数据库SQL递归查询在不同数据库中的实现方法
-
在oracle 数据库查询的select 查询字段中关联其他表的方法
-
java使用插件pagehelper在mybatis中实现分页查询
-
在查询结果中添加一列表示记录的行数的sql语句
-
SQL Server使用Merge语句当源表数据集为空时,无法进行查询的问题
-
在Laravel5.6中使用Swoole的协程数据库查询
-
SqlServer2005中使用row_number()在一个查询中删除重复记录的方法
-
在SQL中使用convert函数进行日期的查询的代码
-
SQL中WHERE变量IS NULL条件导致全表扫描问题的解决方法