经典存储过程，（.NET存储过程分页）

create proc proUserInfo
as
select * from T_UserInfo;
go
exec proUserInfo
--创建带输入参数的存储过程
create proc proUserInfo1
@name varchar(20)
as
select * from T_UserInfo WHERE Name=@name
go
--调用存储过程
exec proUserInfo1 '郭阳'
--创建带2个输入参数的存储过程
create proc proUserInfo2
@name varchar(20),
@age int
as
select * from T_UserInfo where Name=@name or Age=@age
go
exec proUserInfo2 '李哲',18
--带输出参数的存储过程
create proc proUserInfo3
@name varchar(20),
@age int output
as
select @age=age from T_UserInfo WHERE Name=@name
GO

begin
declare @age int
exec proUserInfo3 '李哲',@age output
select @age
end
---------------------分页
select * from (select ROW_NUMBER() over (order by UserId)as
 rownum,* from T_UserInfo) t where rownum>3 and rownum<=6
 -------------------存储过程分页
  create proc profenye
 @pagesize int,
 @pageindex int
 as
 select * from (select ROW_NUMBER() over(order by UserId)as
 rownum,* from T_UserInfo)t where
 rownum>(@pageindex-1)*@pagesize
 and
 rownum<=@pageindex*@pagesize
 go
 --调用
 exec profenye 3,1

--索引
删除重复数据的方法：
  select distinct * into newtable from T_GRDA
 --删除原表
 delete from T_GRDA WHERE 1=1
 --将临时表数据导入到原表中
 insert into T_GRDA
 SELECT * FROM newtable
 //删除临时表

 drop table newtable

------------------------------------------------------------

public partial class 存储过程分页 : Form
    {
        int pagesize = 4;
        int pageindex = 1;
        int recordcount = 0;
        int totalsize = 0;
        public 存储过程分页()
        {
            InitializeComponent();
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {
            pageindex = 1;
            DataBinds(pageindex);
        }

        private void btnPre_Click(object sender, EventArgs e)
        {
            if (pageindex > 1)
            {
                pageindex--;
            }
            
            DataBinds(pageindex);
        }

        private void btnNext_Click(object sender, EventArgs e)
        {

            if (pageindex < totalsize)
            {
                pageindex++;
            }            
            DataBinds(pageindex);
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            DataBinds(totalsize);
        }

        private void 存储过程分页_Load(object sender, EventArgs e)
        {
            recordcount= GetCount();
            if (recordcount % pagesize != 0)
            {
                totalsize = recordcount / pagesize + 1;
            }
            else
            {
                totalsize = recordcount / pagesize;
            }
            DataBinds(pageindex);
        }
        private void DataBinds(int pageindex)
        {
            SqlConnection conn = new SqlConnection(@"Data Source=YHB-PC\SQLSERVER2008;Initial Catalog=MyFirst;Persist Security Info=True;User ID=sa;Password=yhb@163");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();
            cmd.CommandText = "profenye";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@pageindex",pageindex);
            cmd.Parameters.AddWithValue("@pagesize", pagesize);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            this.dataGridView1.DataSource = dt;
            conn.Close();
            conn.Dispose();
        }

        //获取记录数量
        private int GetCount()
        {
            SqlConnection conn = new SqlConnection(@"Data Source=YHB-PC\SQLSERVER2008;Initial Catalog=MyFirst;Persist Security Info=True;User ID=sa;Password=yhb@163");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();
            cmd.CommandText = "select count(*) from T_UserInfo";
            int count=Convert.ToInt32(cmd.ExecuteScalar());
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return count;
        }
    }