今天自己写了一个分页的存储过程来练习一下，该存储过程一共有7个参数，其中最后一个参数的OUTPUT，用于返回一共得页数
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[PagingProc]
@PageIndex INT, --当前页码从0开始
@PageSize INT, --每页的大小
@TableName NVARCHAR(100), --表名称
@Orders NVARCHAR(100), --排序
@Columns NVARCHAR(100), --需要检索的列集合，中间用英文逗号隔开e.g.:ID,NAME
@Filters NVARCHAR(100), --过滤条件语句
@TotalPages INT OUTPUT
AS
BEGIN
DECLARE @SQL NVARCHAR(200) --查询当前页所有记录的sql语句
DECLARE @PAGESSQL NVARCHAR(200) --查询行数的sql语句
DECLARE @TOTALCOUNT INT --一共得行数，用于计算所总页数
SET NOCOUNT ON
IF @Filters <> ''
SET @PAGESSQL = 'SELECT @TOTALCOUNT = COUNT(*) FROM ' + @TableName + ' WHERE ' + @Filters
ELSE
SET @PAGESSQL = 'SELECT @TOTALCOUNT = COUNT(*) FROM ' + @TableName
EXEC SP_EXECUTESQL @PAGESSQL, N'@TOTALCOUNT INT OUT',@TOTALCOUNT OUT
SET @TotalPages = Ceiling(CONVERT(REAL,@TOTALCOUNT) / CONVERT(REAL,@PageSize))--计算页数
SET @SQL='SELECT TOP ' + CAST(@PageSize as varchar(10)) + ' ' + @Columns + ' FROM ' + @TableName + '
WHERE ID NOT IN
(
SELECT TOP ' + CAST(@PageIndex * @PageSize as varchar(10)) + ' ID
FROM ' + @TableName
IF @Filters <> ''
SET @SQL = @SQL + ' WHERE ' + @Filters
IF @Orders <> ''
SET @SQL = @SQL + ' ORDER BY ' + @ORDERS
SET @SQL = @SQL + ')'
IF @Filters <> ''
SET @SQL = @SQL + ' AND ' + @Filters
IF @Orders <> ''
SET @SQL = @SQL + ' ORDER BY ' + @ORDERS
EXEC(@SQL)
END
下面是C#代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace PagingProcedure
{
class Program
{
static void Main(string[] args)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=Rap_Kevin;Integrated Security=True;";
System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "pagingproc";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PageIndex", System.Data.SqlDbType.Int));
cmd.Parameters["@PageIndex"].Value = 0;
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PageSize", System.Data.SqlDbType.Int));
cmd.Parameters["@PageSize"].Value = 5;
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@TableName", System.Data.SqlDbType.VarChar, 100));
cmd.Parameters["@TableName"].Value = "Sells";
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Orders", System.Data.SqlDbType.VarChar, 100));
cmd.Parameters["@Orders"].Value = "LastModifyTime DESC";
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Columns", System.Data.SqlDbType.VarChar, 100));
cmd.Parameters["@Columns"].Value = "*";
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Filters", System.Data.SqlDbType.VarChar, 100));
cmd.Parameters["@Filters"].Value = "";
cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@TotalPages", System.Data.SqlDbType.Int));
cmd.Parameters["@TotalPages"].Value = 0;
cmd.Parameters["@TotalPages"].Direction = System.Data.ParameterDirection.InputOutput;
conn.Open();
System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
System.Console.WriteLine((Guid)reader[0]);
}
reader.Close();
System.Console.WriteLine(cmd.Parameters["@TotalPages"].Value);
conn.Close();
}
}
}
之前弄错了，执行一次是可以获得结果集，同时也能取出OUTPUT参数的值的。
