22,209
社区成员
发帖
与我相关
我的任务
分享
declare @tablename nvarchar(100)
--创建临时表
select *,[count] into #emd_tasks from EDM_Tasks
declare @cursor cursor
set @cursor=cursor for select name from EDM_Tasks
open @cursor
fetch next from @cursor into @tablename
while(@@fetch_status=0)begin
begin
--print 'update #emd_tasks set [count] = (select count(1) from '+ @tablename+') where TaskTableName = '''+@tablename+''''
exec('update #emd_tasks set [count] = (select count(1) from '+ @tablename+') where TaskTableName = '''+@tablename+'''')
fetch next from @cursor into @tablename
end
close @cursor
deallocate @cursor
select * from #emd_tasks
select @tablename = TaskTableName FROM task WHERE ...
EXEC ('SELECT * FROM ' + @tablename)
USE [TopenEDM]
GO
/****** Object: StoredProcedure [dbo].[EDM_TasksList] Script Date: 06/24/2012 09:21:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EDM_TasksList]
--TaskID, TaskChannels, Task_ReplyTo, TaskSendToGroups, TaskExclusionsGroups, Task_EMID, TaskSendDateTime, TaskTableName, TaskState, Task_CreateUID, TaskCreateDate
@PageSize INT,
@PageIndex INT,
@RecordCount INT OUTPUT
AS
BEGIN
-- BEGIN TRAN
DECLARE @Sql NVARCHAR(MAX), @Where NVARCHAR(MAX),@taskTableName nvarchar(50),@count int,@tabsql nvarchar(max)
,@num int
--默认值 @SQL 语句取得总行数
SET @Sql = 'SELECT @RecordCount = COUNT(1) FROM EDM_Tasks '
set @Where=''
IF(@Where != '') SET @Where = ' WHERE ' + @Where
--合并 @SQL 查询行数
SET @Sql = @Sql + @Where
--合并 @SQL 查询
SET @Sql = 'SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY TaskID DESC) AS RowNumber, EDM_Tasks.*,dbo.EDM_FN_GetGroupsNameByIDs(EDM_Tasks.TaskSendToGroups,null,null) AS SendGroupsName,dbo.EDM_FN_GetGroupsNameByIDs(EDM_Tasks.TaskExclusionsGroups,null,null) AS ExclusionsGroupsName,dbo.EDM_GetEmailIDByName(EDM_Tasks.Task_EMID) as TaskEmailName FROM EDM_Tasks ' + @Where + ') as tmp
WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize'
EXEC sp_executesql @Sql,
N'@PageSize INT,
@PageIndex INT',
@PageSize,
@PageIndex
END