在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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐