文章目录
一个分页用的存储过程(转)
写了一个分页用的存储过程感觉还蛮好好用
-- =============================================
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'ChyPage'
AND type = 'P')
DROP PROCEDURE ChyPage
GO
/*
*@curentpage 当前页
*@pagesize 每页记录数
*@TableName 表名
*@key 主键(自动排序)
*@where 查询条件
1)空为 null
2)有查询条件不要带where
*@order '0'表示 desc '1'是asc
*@pageCount 总页数
*/
create procedure ChyPage
@currentpage int,@pagesize int,
@TableName varchar(20),@key varchar(50),
@where varchar(30),@order varchar(1),
@pageCount int output ,@str varchar(300) output
as
begin
---------------执行的sql语句------------
declare @sql nvarchar(400),@ordreby nvarchar(200)
declare @tempsql1 varchar(200),@tempsql2 varchar(200)
---------------记录总数-----------------
declare @count int
---------------临时变量------------------------
declare @temp1 int,@temp2 int
set @TableName=' [email=]'+@TableName+'[/email] '
set @key=' [email=]'+@key+'[/email] '
if @order='0'
set @ordreby='order by [email=]'+@key+'desc'[/email]
else
set @ordreby='order by [email=]'+@key[/email]
if @where='null'
set @sql='select @count = count(*) from '+ @TableName
else
set @sql='select @count = count(*) from '+ @TableName+' where [email=]'+@where[/email]
------------@count 付值(声明变量@count 在说明是output 内型)---------------------------
exec sp_executesql @sql,N'@count int out',@count out
------------求总页数------------------------------
if (@count%@pagesize)=0
set @pagecount=@count/@pagesize
else
set @pagecount=@count/@pagesize+1
-----------判断显示当前页是否异常------------------
if @currentpage>@pagecount
set @currentpage=@pagecount
if @currentpage<1
set @currentpage=1
----------记录数小于页面显示记录数-----------------
if(@currentpage=1)
begin
if @where='null'
set @where=' '
else
set @where=' where [email=]'+@where[/email]
set @sql = 'select top'+ str(@pagesize)+' * from [email=]'+@TableName+@where+@ordreby[/email]
end
else
begin
/* ---------------desc----------------------
*@temp1表示前面的记录
*@temp2表示后面的记录
*假设一共77个记录,每次取10个。取67~58(第2页),去掉前面的57(1~57)个和后面的10个(77~66)
*/
if @order=0
begin
set @temp1 = @count-@currentpage*@pagesize
if @temp1<0
set @temp1=0
set @temp2 = (@currentpage - 1)*@pagesize
if @where='null'
begin
set @tempsql1='select top ' + str(@temp1)+' [email=]'+@key+'[/email] from ' + @TableName+' order by ' +@key
set @tempsql2='select top ' + str(@temp2)+' [email=]'+@key+'[/email] from ' + @TableName + @ordreby
end
else
begin
set @tempsql1='select top ' + str(@temp1)+'
[email=]'+@key+'[/email] from ' + @TableName+' where
[email=]'+@where+'[/email] order by ' +@key
set @tempsql2='select top ' + str(@temp2)+'
[email=]'+@key+'[/email] from ' + @TableName+' where
[email=]'+@where+@ordreby[/email]
end
set @sql=' select top ' + str(@pagesize) + ' * from ' + @TableName + ' where [email=]'+@key[/email]+ ' not in '
set @sql= @sql+' ( '+ @tempsql1 +' ) and '
set @sql= @sql+@key+ ' not in ( [email=]'+@tempsql2[/email] +' ) '
if @where='null'
set @sql= @sql+@ordreby
else
set @sql= @sql+' and [email=]'+@where+@ordreby[/email]
end
/* ----------------asc---------------------
* @temp 表示前面显示的记录总数
* 去掉 @temp 在取出 pagesize 个即可
*/
else
begin
set @temp1=(@currentpage-1)*@pagesize
if @where='null'
set @tempsql1='select top '+ str(@temp1)+' [email=]'+@key+'[/email] from ' + @TableName + @ordreby
else
set @tempsql1='select top '+ str(@temp1)+'
[email=]'+@key+'[/email] from ' + @TableName ++' where
[email=]'+@where+@ordreby[/email]
set @sql=' select top ' + str(@pagesize) + ' * from ' + @TableName + ' where [email=]'+@key[/email]+ ' not in '
set @sql=@sql+' ( [email=]'+@tempsql1+'[/email] ) '
if @where='null'
set @sql= @sql+@ordreby
else
set @sql= @sql+' and [email=]'+@where+@ordreby[/email]
end
/* -------------------------------------*/
end
set @str=@sql
exec sp_executesql @sql
end
go
------------------------------执行存储过程------------------------------------------------
declare @str varchar(300),@pageCount int
exec ChyPage -1,5,'products','productid','productid<=48','1',@pageCount output,@str output
print '总页数:'+str(@pagecount)
print @str
-- Create procedure basic template
-- =============================================
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'ChyPage'
AND type = 'P')
DROP PROCEDURE ChyPage
GO
/*
*@curentpage 当前页
*@pagesize 每页记录数
*@TableName 表名
*@key 主键(自动排序)
*@where 查询条件
1)空为 null
2)有查询条件不要带where
*@order '0'表示 desc '1'是asc
*@pageCount 总页数
*/
create procedure ChyPage
@currentpage int,@pagesize int,
@TableName varchar(20),@key varchar(50),
@where varchar(30),@order varchar(1),
@pageCount int output ,@str varchar(300) output
as
begin
---------------执行的sql语句------------
declare @sql nvarchar(400),@ordreby nvarchar(200)
declare @tempsql1 varchar(200),@tempsql2 varchar(200)
---------------记录总数-----------------
declare @count int
---------------临时变量------------------------
declare @temp1 int,@temp2 int
set @TableName=' [email=]'+@TableName+'[/email] '
set @key=' [email=]'+@key+'[/email] '
if @order='0'
set @ordreby='order by [email=]'+@key+'desc'[/email]
else
set @ordreby='order by [email=]'+@key[/email]
if @where='null'
set @sql='select @count = count(*) from '+ @TableName
else
set @sql='select @count = count(*) from '+ @TableName+' where [email=]'+@where[/email]
------------@count 付值(声明变量@count 在说明是output 内型)---------------------------
exec sp_executesql @sql,N'@count int out',@count out
------------求总页数------------------------------
if (@count%@pagesize)=0
set @pagecount=@count/@pagesize
else
set @pagecount=@count/@pagesize+1
-----------判断显示当前页是否异常------------------
if @currentpage>@pagecount
set @currentpage=@pagecount
if @currentpage<1
set @currentpage=1
----------记录数小于页面显示记录数-----------------
if(@currentpage=1)
begin
if @where='null'
set @where=' '
else
set @where=' where [email=]'+@where[/email]
set @sql = 'select top'+ str(@pagesize)+' * from [email=]'+@TableName+@where+@ordreby[/email]
end
else
begin
/* ---------------desc----------------------
*@temp1表示前面的记录
*@temp2表示后面的记录
*假设一共77个记录,每次取10个。取67~58(第2页),去掉前面的57(1~57)个和后面的10个(77~66)
*/
if @order=0
begin
set @temp1 = @count-@currentpage*@pagesize
if @temp1<0
set @temp1=0
set @temp2 = (@currentpage - 1)*@pagesize
if @where='null'
begin
set @tempsql1='select top ' + str(@temp1)+' [email=]'+@key+'[/email] from ' + @TableName+' order by ' +@key
set @tempsql2='select top ' + str(@temp2)+' [email=]'+@key+'[/email] from ' + @TableName + @ordreby
end
else
begin
set @tempsql1='select top ' + str(@temp1)+'
[email=]'+@key+'[/email] from ' + @TableName+' where
[email=]'+@where+'[/email] order by ' +@key
set @tempsql2='select top ' + str(@temp2)+'
[email=]'+@key+'[/email] from ' + @TableName+' where
[email=]'+@where+@ordreby[/email]
end
set @sql=' select top ' + str(@pagesize) + ' * from ' + @TableName + ' where [email=]'+@key[/email]+ ' not in '
set @sql= @sql+' ( '+ @tempsql1 +' ) and '
set @sql= @sql+@key+ ' not in ( [email=]'+@tempsql2[/email] +' ) '
if @where='null'
set @sql= @sql+@ordreby
else
set @sql= @sql+' and [email=]'+@where+@ordreby[/email]
end
/* ----------------asc---------------------
* @temp 表示前面显示的记录总数
* 去掉 @temp 在取出 pagesize 个即可
*/
else
begin
set @temp1=(@currentpage-1)*@pagesize
if @where='null'
set @tempsql1='select top '+ str(@temp1)+' [email=]'+@key+'[/email] from ' + @TableName + @ordreby
else
set @tempsql1='select top '+ str(@temp1)+'
[email=]'+@key+'[/email] from ' + @TableName ++' where
[email=]'+@where+@ordreby[/email]
set @sql=' select top ' + str(@pagesize) + ' * from ' + @TableName + ' where [email=]'+@key[/email]+ ' not in '
set @sql=@sql+' ( [email=]'+@tempsql1+'[/email] ) '
if @where='null'
set @sql= @sql+@ordreby
else
set @sql= @sql+' and [email=]'+@where+@ordreby[/email]
end
/* -------------------------------------*/
end
set @str=@sql
exec sp_executesql @sql
end
go
------------------------------执行存储过程------------------------------------------------
declare @str varchar(300),@pageCount int
exec ChyPage -1,5,'products','productid','productid<=48','1',@pageCount output,@str output
print '总页数:'+str(@pagecount)
print @str
//得到标题分页数据
public static string getTitlePagiNationData(string storeName, string kindid, int page,string where)
{
SqlParameter[] p = new SqlParameter[8];
p[0] = new SqlParameter();
p[0].ParameterName = "@currentpage";
p[1] = new SqlParameter();
p[1].ParameterName = "@pagesize";
p[2] = new SqlParameter();
p[2].ParameterName = "@TableName";
p[3] = new SqlParameter();
p[3].ParameterName = "@key";
p[4] = new SqlParameter();
p[4].ParameterName = "@where";
p[5] = new SqlParameter();
p[5].ParameterName = "@order";
p[6] = new SqlParameter();
p[6].ParameterName = "@pageCount";
p[7] = new SqlParameter();
p[7].ParameterName = "@str";
p[0].SqlDbType = p[1].SqlDbType = p[6].SqlDbType = SqlDbType.Int;
p[2].SqlDbType = p[3].SqlDbType = p[4].SqlDbType = p[5].SqlDbType = p[7].SqlDbType = SqlDbType.VarChar;
p[2].Size = p[3].Size = 30;
p[4].Size = 50;
p[5].Size = 1;
p[7].Size = 450;
p[0].Value = page;
p[1].Value = BBSClass.Titliepagesize;
p[2].Value = tablename;
p[3].Value = "title_id";
p[4].Value = where + kindid;
p[5].Value = '0';
p[6].Value = 0;
p[7].Direction = ParameterDirection.Output;
SqlCommand cmd = DataHelpe.GetCommand(storeName, p);
cmd.ExecuteNonQuery();
return cmd.Parameters["@str"].Value.ToString();
public static string getTitlePagiNationData(string storeName, string kindid, int page,string where)
{
SqlParameter[] p = new SqlParameter[8];
p[0] = new SqlParameter();
p[0].ParameterName = "@currentpage";
p[1] = new SqlParameter();
p[1].ParameterName = "@pagesize";
p[2] = new SqlParameter();
p[2].ParameterName = "@TableName";
p[3] = new SqlParameter();
p[3].ParameterName = "@key";
p[4] = new SqlParameter();
p[4].ParameterName = "@where";
p[5] = new SqlParameter();
p[5].ParameterName = "@order";
p[6] = new SqlParameter();
p[6].ParameterName = "@pageCount";
p[7] = new SqlParameter();
p[7].ParameterName = "@str";
p[0].SqlDbType = p[1].SqlDbType = p[6].SqlDbType = SqlDbType.Int;
p[2].SqlDbType = p[3].SqlDbType = p[4].SqlDbType = p[5].SqlDbType = p[7].SqlDbType = SqlDbType.VarChar;
p[2].Size = p[3].Size = 30;
p[4].Size = 50;
p[5].Size = 1;
p[7].Size = 450;
p[0].Value = page;
p[1].Value = BBSClass.Titliepagesize;
p[2].Value = tablename;
p[3].Value = "title_id";
p[4].Value = where + kindid;
p[5].Value = '0';
p[6].Value = 0;
p[7].Direction = ParameterDirection.Output;
SqlCommand cmd = DataHelpe.GetCommand(storeName, p);
cmd.ExecuteNonQuery();
return cmd.Parameters["@str"].Value.ToString();
}
/// <summary>
/// 调用存储过程创建一个SqlCommand对象
/// </summary>
/// <param name="procName">存储过程</param>
/// <param name="prams">给存储过程传递传输SqlParameter对象</param>
/// <returns>SqlCommand</returns>
public static SqlCommand GetCommand(string storeName, SqlParameter[] prams)
{
SqlConnection connection = new SqlConnection(m_constr);
try
{
SqlCommand cmd = new SqlCommand(storeName, connection); //存储过程名
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
if (prams != null)
{
foreach (SqlParameter item in prams)
{
cmd.Parameters.Add(item);
}
}
cmd.Prepare();
return cmd;
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
}
/// 调用存储过程创建一个SqlCommand对象
/// </summary>
/// <param name="procName">存储过程</param>
/// <param name="prams">给存储过程传递传输SqlParameter对象</param>
/// <returns>SqlCommand</returns>
public static SqlCommand GetCommand(string storeName, SqlParameter[] prams)
{
SqlConnection connection = new SqlConnection(m_constr);
try
{
SqlCommand cmd = new SqlCommand(storeName, connection); //存储过程名
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
if (prams != null)
{
foreach (SqlParameter item in prams)
{
cmd.Parameters.Add(item);
}
}
cmd.Prepare();
return cmd;
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
}