通用的查询存储过程

这是一个封装好的通用的存储过程，功能是查任意表任意视图带任意条件按任意规则查任意页数每页任意行数。

CREATE PROCEDURE [dbo].[Common_LoadData]
(
@tablename nvarchar(50),–表名或视图名称
@PageNumber int,–当前是第几页
@rows int,–每页显示的记录数
@field nvarchar(1000)=’*’,–要读取的字段列表
@id nvarchar (50),–依据什么字段来排序,一般是主键
@where nvarchar (1000),–条件不要带where
@gz nvarchar (1000),–按什么来排序
@indexName nvarchar (50)–索引名称
)
as
begin
declare @cmd nvarchar (4000)
if rtrim(ltrim(@gz))=’’
begin
set @gz=’ order by '+@id
end

set @indexName=isnull(@indexName,’’)
if @indexName !=’’
set @indexName=’ with(index =’+@indexName+’) ’

if (@where is not null and rtrim(ltrim(@where)) !=’’ )
begin
if @rows=0 or @PageNumber=0 --读取第页第行,表示要读取全部的记录
begin
set @cmd=‘select ‘+@field+’ from ‘+@tablename+@indexName +’ where ‘+@where+’ ‘+@gz
end
else
begin
set @cmd=‘select top ‘+cast(@rows as nvarchar )+’ ‘+@field+’ from (’+‘select row_number() over (’+@gz+’ ) as Nid,’+@field+’ from ‘+@tablename+@indexName +’ where ‘+@where+’) as T '+ ’ where Nid > ‘+cast(@rows*(@PageNumber -1) as nvarchar)+’ and ‘+@where+’ '+@gz
end
end
else
begin
if @rows=0 or @PageNumber=0 --读取第页第行,表示要读取全部的记录
begin
set @cmd='select ‘+@field+’ from ‘+@tablename+@indexName +’ ‘+@gz
end
else
begin
set @cmd=‘select top ‘+cast(@rows as nvarchar )+’ ‘+@field+’ from (’+‘select row_number() over ( ‘+@gz+’ ) as Nid,’+@field+’ from ‘+@tablename+@indexName +’ ) as T ‘+’ where Nid > ‘+cast(@rows*(@PageNumber -1) as nvarchar)+’ '+@gz
end
end
exec sp_executesql @cmd
–print @cmd

end

/*
exec Common_LoadData @PageNumber=1,@rows=100,@tableName=N’[OAMessage]’,@id=N’MsgID’,@where=N’’,@gz=N’’,@indexName=NULL,@field=N’’
select top 10 * from (select row_number() over ( order by MsgID ) as Nid, from [OAMessage] ) as T where Nid > 0 order by MsgID

*/

GO