最近找到一很好的SQL存储过程,用过都觉得很不错,执行效率也可以,在此推荐给大家.
——缘易
CREATE
proc
up_GetTopicList
@a_TableList Varchar ( 200 ), --要查询的字段
@a_TableName Varchar ( 30 ), --要查询的表名
@a_SelectWhere Varchar ( 500 ), --查询限制条件
@a_SelectOrderId Varchar ( 20 ), --查询主键
@a_SelectOrder Varchar ( 50 ), --排序字段
@a_intPageNo int ,
@a_intPageSize int ,
@RecordCount int OUTPUT
as
/**/ /*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intRootRecordCount int
declare @intRowCount int
declare @TmpSelect NVarchar ( 600 )
/**/ /*关闭计数*/
set nocount on
/**/ /*求总共根贴数*/
select @TmpSelect = ' set nocount on;select @SPintRootRecordCount = count(*) from ' + @a_TableName + ' ' + @a_SelectWhere
execute sp_executesql
@TmpSelect ,
N ' @SPintRootRecordCount int OUTPUT ' ,
@SPintRootRecordCount = @intRootRecordCount OUTPUT
select @RecordCount = @intRootRecordCount
if ( @intRootRecordCount = 0 ) -- 如果没有贴子,则返回零
return 0
/**/ /*判断页数是否正确*/
if ( @a_intPageNo - 1 ) * @a_intPageSize > @intRootRecordCount
return ( - 1 )
/**/ /*求开始rootID*/
set @intRowCount = ( @a_intPageNo - 1 ) * @a_intPageSize + 1
/**/ /*限制条数*/
select @TmpSelect = ' set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = ' + @a_SelectOrderId + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' ' + @a_SelectOrder
execute sp_executesql
@TmpSelect ,
N ' @SPintRowCount int,@SPintBeginID int OUTPUT ' ,
@SPintRowCount = @intRowCount , @SPintBeginID = @intBeginID OUTPUT
/**/ /*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/**/ /*限制条数*/
select @TmpSelect = ' set nocount on;set rowcount @SPintRowCount;select @SPintEndID = ' + @a_SelectOrderId + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' ' + @a_SelectOrder
execute sp_executesql
@TmpSelect ,
N ' @SPintRowCount int,@SPintEndID int OUTPUT ' ,
@SPintRowCount = @intRowCount , @SPintEndID = @intEndID OUTPUT
if @a_SelectWhere = '' or @a_SelectWhere IS NULL
select @TmpSelect = ' set nocount off;set rowcount 0;select ' + @a_TableList + ' from ' + @a_TableName + ' where ' + @a_SelectOrderId + ' between '
else
select @TmpSelect = ' set nocount off;set rowcount 0;select ' + @a_TableList + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' and ' + @a_SelectOrderId + ' between '
if @intEndID > @intBeginID
select @TmpSelect = @TmpSelect + ' @SPintBeginID and @SPintEndID ' + ' ' + @a_SelectOrder
else
select @TmpSelect = @TmpSelect + ' @SPintEndID and @SPintBeginID ' + ' ' + @a_SelectOrder
execute sp_executesql
@TmpSelect ,
N ' @SPintEndID int,@SPintBeginID int ' ,
@SPintEndID = @intEndID , @SPintBeginID = @intBeginID
return ( @@rowcount )
-- select @@rowcount
GO
@a_TableList Varchar ( 200 ), --要查询的字段
@a_TableName Varchar ( 30 ), --要查询的表名
@a_SelectWhere Varchar ( 500 ), --查询限制条件
@a_SelectOrderId Varchar ( 20 ), --查询主键
@a_SelectOrder Varchar ( 50 ), --排序字段
@a_intPageNo int ,
@a_intPageSize int ,
@RecordCount int OUTPUT
as
/**/ /*定义局部变量*/
declare @intBeginID int
declare @intEndID int
declare @intRootRecordCount int
declare @intRowCount int
declare @TmpSelect NVarchar ( 600 )
/**/ /*关闭计数*/
set nocount on
/**/ /*求总共根贴数*/
select @TmpSelect = ' set nocount on;select @SPintRootRecordCount = count(*) from ' + @a_TableName + ' ' + @a_SelectWhere
execute sp_executesql
@TmpSelect ,
N ' @SPintRootRecordCount int OUTPUT ' ,
@SPintRootRecordCount = @intRootRecordCount OUTPUT
select @RecordCount = @intRootRecordCount
if ( @intRootRecordCount = 0 ) -- 如果没有贴子,则返回零
return 0
/**/ /*判断页数是否正确*/
if ( @a_intPageNo - 1 ) * @a_intPageSize > @intRootRecordCount
return ( - 1 )
/**/ /*求开始rootID*/
set @intRowCount = ( @a_intPageNo - 1 ) * @a_intPageSize + 1
/**/ /*限制条数*/
select @TmpSelect = ' set nocount on;set rowcount @SPintRowCount;select @SPintBeginID = ' + @a_SelectOrderId + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' ' + @a_SelectOrder
execute sp_executesql
@TmpSelect ,
N ' @SPintRowCount int,@SPintBeginID int OUTPUT ' ,
@SPintRowCount = @intRowCount , @SPintBeginID = @intBeginID OUTPUT
/**/ /*结束rootID*/
set @intRowCount = @a_intPageNo * @a_intPageSize
/**/ /*限制条数*/
select @TmpSelect = ' set nocount on;set rowcount @SPintRowCount;select @SPintEndID = ' + @a_SelectOrderId + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' ' + @a_SelectOrder
execute sp_executesql
@TmpSelect ,
N ' @SPintRowCount int,@SPintEndID int OUTPUT ' ,
@SPintRowCount = @intRowCount , @SPintEndID = @intEndID OUTPUT
if @a_SelectWhere = '' or @a_SelectWhere IS NULL
select @TmpSelect = ' set nocount off;set rowcount 0;select ' + @a_TableList + ' from ' + @a_TableName + ' where ' + @a_SelectOrderId + ' between '
else
select @TmpSelect = ' set nocount off;set rowcount 0;select ' + @a_TableList + ' from ' + @a_TableName + ' ' + @a_SelectWhere + ' and ' + @a_SelectOrderId + ' between '
if @intEndID > @intBeginID
select @TmpSelect = @TmpSelect + ' @SPintBeginID and @SPintEndID ' + ' ' + @a_SelectOrder
else
select @TmpSelect = @TmpSelect + ' @SPintEndID and @SPintBeginID ' + ' ' + @a_SelectOrder
execute sp_executesql
@TmpSelect ,
N ' @SPintEndID int,@SPintBeginID int ' ,
@SPintEndID = @intEndID , @SPintBeginID = @intBeginID
return ( @@rowcount )
-- select @@rowcount
GO