SQL存储过程实例

--1.存储过程复用代码案例--如果english不及格的人超过半数
--则给每个人增加2分,循环加,直到不及格的人数少于一半.
--usp_promoteGrade有一个参数,及格分数线@passline.

use MySchool
select * from Score
update Score set english=50 where english between 60 and 90
create proc usp_promoteGrade
@passline int
as
begin
declare @count1 int=(select COUNT(*) from Score)
declare @loster int=(select COUNT(*) from Score where english<@passline)
declare @harfcount int =floor(@count1/2)
while @harfcount<@loster
begin
update Score set english+=2 where english<@passline
set @loster=(select COUNT(*) from Score where english<@passline)
end
end

exec usp_promoteGrade 60

--2.ADO中使用存储过程
--2.1-1写usp_Login存储过程,参数有三个,其中第三个参数为output
--用来返加登陆是否成功,成功1,失败0(@uname varchar(50),
--@password varchar(50),@result bit output)
create table T_user
(
FuseName nvarchar(50),
Fpassword varchar(50)
)
insert into T_user values('admin','888888')
select * from T_user
create proc usp_Login
@useName varchar(50),@password varchar(50),@result bit output
as
begin

declare @count int
set @count=(select COUNT(*) from T_user where FuseName=@useName and Fpassword=@password)
if @count>0
begin
set @result=1
end
else
begin
set @result=0
end

end

declare @r bit
exec usp_Login 'admin','888888',@r output
print @r

--2.1-2在login.aspx中使用设计登陆界面,使用以上存储过程.

protected void btn_Click(object sender, EventArgs e)
{

string usename = useName.Text.Trim();
string pwd = password.Text.Trim();

string conStr = ConfigurationManager.ConnectionStrings["sqlservercon"].ConnectionString;
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
string use_name = "usp_Login";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = use_name;
SqlParameter prm1 = new SqlParameter("@useName", usename);
SqlParameter prm2 = new SqlParameter("@password", pwd);
SqlParameter prm3 = new SqlParameter("@result", System.Data.SqlDbType.Bit);
prm3.Direction = ParameterDirection.Output;
cmd.Parameters.Add(prm1);
cmd.Parameters.Add(prm2);
cmd.Parameters.Add(prm3);

int r = cmd.ExecuteNonQuery();
Label1.Text =r.ToString();
bool b = Convert.ToBoolean(prm3.Value);
if (b==true)
{
Label2.Text = "登录成功";
}
else
{
Label2.Text = "登录失败";
}
}
}
}

--2.2-1写usp_chkLogin只有两个参数没有output参数.
use MySchool
select * from T_user
create proc usp_chkLogin
@useName1 varchar(50),
@password1 varchar(50)
as
begin
select COUNT(*) from T_user where FuseName=@useName1 and Fpassword=@password1
end

exec usp_chkLogin 'admin','888888'

--2.1-2写login.aspx中使用设计登陆界面,使用以上存储过程.

protected void btn_Click(object sender, EventArgs e)
{

string usename = useName.Text.Trim();
string pwd = password.Text.Trim();

string conStr = ConfigurationManager.ConnectionStrings["sqlservercon"].ConnectionString;
using (SqlConnection conn = new SqlConnection(conStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
string use_name = "usp_chkLogin";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = use_name;
SqlParameter prm1 = new SqlParameter("@useName1", usename);
SqlParameter prm2 = new SqlParameter("@password1", pwd);
cmd.Parameters.Add(prm1);
cmd.Parameters.Add(prm2);

object obj = cmd.ExecuteScalar();
int b = Convert.ToInt32(obj);
if (b>0)
{
Label2.Text = "登录成功";
}
else
{
Label2.Text = "登录失败";
}
}
}


}

--3.写插入存储过程,并在注册页面中使用.

create proc usp_T_user_Insert
@useName2 varchar(50),
@password2 varchar(50)
as
begin
insert into T_user values(@useName2,@password2)
end

exec usp_T_user_Insert 'shang','111111'
select * from T_user
--4.写删除存储过程,并在注册页面中使用.

create proc usp_T_user_Delete
@useName3 varchar(50),
@password3 varchar(50)
as
begin
delete from T_user where FuseName=@useName3 and Fpassword=@password3
end

--5.写查询存储过程,在注册页面中使用.(选做可以用winfrom实现
--dataGradView)

create proc usp_T_user_Select
@useName4 varchar(50),
@password4 varchar(50)
as
begin
select * from T_user where FuseName=@useName4 and Fpassword=@password4
end




private void button1_Click(object sender, EventArgs e)
{
string usename = useName.Text.Trim();
string pwd = password.Text.Trim();

DataSet dataset = new DataSet();//在内存中开辟空间给 dataset
string ConnStr = "Data Source=EJNSWJOZ0JSDS7J;Initial Catalog=MySchool;Persist Security Info=True;User ID=sa;Password=111111";
using (SqlConnection conn = new SqlConnection(ConnStr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
string use_name = "usp_T_user_Select";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = use_name;
SqlParameter prm1 = new SqlParameter("@useName3", usename);
SqlParameter prm2 = new SqlParameter("@password3", pwd);

cmd.Parameters.Add(prm1);
cmd.Parameters.Add(prm2);

SqlDataAdapter adapeter = new SqlDataAdapter(cmd);//把数据库服务器中的查询结果给转接器 adapter
adapeter.Fill(dataset);//adapter把查询结果交给dataset

dataGridView1.DataSource=adapeter;

}

}
}