复制代码 代码如下:

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查询中使用表变量】相关文章：

★ sql 查询慢的原因分析

★ 搜索sql语句

★ 在查询结果中添加一列表示记录的行数的sql语句

★ SqlServer查询表中各列名称、表中列数

★ 模糊查询

★ 查询表的操作记录的sql

★ SQL 模糊查询

★ sql server 2000 版本查询

★ SqlServer 2005的排名函数使用小结

★ MSSQL 游标使用 心得