在sql查詢中使用表變量
更新時間:2010年06月21日 12:07:50 作者:
在sql查詢中使用表變量,需要的朋友可以參考下。
復(fù)制代碼 代碼如下:
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: 獲取工作任務(wù)單列表,根據(jù)相關(guān)條件
-- =============================================
ALTER PROCEDURE [dbo].[PROG_WORKTASK_List]
@userID varchar(10), ---當(dāng)前用戶
@status int, ---狀態(tài)
@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
相關(guān)文章
利用ROW_NUMBER() OVER函數(shù)給SQL數(shù)據(jù)庫中每一條記錄分配行號的方法
這篇文章主要介紹了利用ROW_NUMBER() OVER函數(shù)給SQL數(shù)據(jù)庫中每一條記錄分配行號的方法,需要的朋友可以參考下2015-10-10SQL?Server下7種“數(shù)據(jù)分頁”方案全網(wǎng)最新最全
這篇文章主要介紹了SQL?Server下7種“數(shù)據(jù)分頁”方案,全網(wǎng)最全,本文下面重點闡述上述【第二種】方案在SQL?Server上的使用(其它種類數(shù)據(jù)庫由于Sql語句略有差異,所以需要調(diào)整,但方案也類似),需要的朋友可以參考下2023-01-01mssql查找備注(text,ntext)類型字段為空的方法
在sql語句中,如果查找某個文本字段值為空的,可以用select * from 表 where 字段='' ,但是如果這個字段數(shù)據(jù)類型是text或者ntext,那上面的sql語句就要出錯了。2008-08-08