SQL Server 存储过程_mysql存储过程教程

CREATE PROC MyPage
(
@name nvarchar(10),
@page decimal output
)
AS
BEGIN
select * from students
END

declare @p decimal--创建Sql变量
declare @n nvarchar(5)
set @n='张三'--为Sql变量赋值
EXEC MyPage @n,@p out--调用存储过程
select @p

alter proc proc_name
as
begin
　　--sql语句
end　　
--删除存储过程
drop proc proc_name

--存储过程分页
alter PROC NesList
(
@a int,--第几页
@b int,--每页行数
@sum int output,--总行数
@str int output,--总页数
@result nvarchar(100) output--结果
)
AS
BEGIN 
--总行数
Select @sum=count(*) from NewsListTable
--总页数
set @str=CEILING(@sum*1.0/@b)
if(@a<0 or @a>@str)
begin
set @result='页码错误！'
end
--分页数据
select t.NewsId as '新闻编号',t.NewsTitle as '新闻类别',t.NewsDate  as '新闻发布时间' from 
( SELECT  *,RN=ROW_NUMBER() OVER(order by NewsId) FROM NewsListTable) as t where t.RN between ((@a-1)*@b)+1 and @a*@b --between and 包括两边值
END
-------测试存储过程
--测试
declare @nub int
set @nub=-1
declare @nm int
set @nm=2
declare @countsum int
declare @countye int
declare @re nvarchar(100)
exec NesList @nub,@nm,@countsum out,@countye out,@re out
select @countsum,@countye,@re

 private int a = 1;//第几页
private int b = 7;//每页数据行数
private int sum;//总数据行数
private int str;//总页数
//利用SqlDataAdapter进行数据读取绑定
string connstr = "*****************";
DataTable u = new DataTable();
using (SqlDataAdapter con = new SqlDataAdapter("USP_Students", connstr))
{ 

con.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter[] pms = { 

new SqlParameter ("@A",SqlDbType.Int) { 
Value=a },
new SqlParameter ("@B",SqlDbType.Int) { 
 Value=b},
new SqlParameter ("@SUM",SqlDbType.Int) { 
 Direction=ParameterDirection.Output},
new SqlParameter ("STR",SqlDbType.Int) { 
 Direction=ParameterDirection.Output}
};
con.SelectCommand.Parameters.AddRange(pms);
con.Fill(u);
lblsum.Text = pms[2].Value.ToString();//获取输出参数
lblye.Text = pms[3].Value.ToString();
label1.Text = a.ToString();
this.DGVI.DataSource = u;
}