----创建一个只有输入参数的存储过程
create procedure proc_user
@name varchar(20),
@Password varchar(100)
as
select * from loginuser
where name like @name
---创建一个有输入和输出参数的存储过程
create procedure proc_usertext
@name varchar(20),
@blog varchar(100) output
as
select @blog = blog from loginuser where name = @name
---创建一个有输入和返回参数的存储过程
create PROCEDURE returnval
@name varchar(100),
@blog varchar(100) output
AS
begin
select @blog = blog from loginuser where name = @name
if(@blog is null)
set @blog = ''你还没有申请博客''
return execute(@blog ) --数据数型的转换
end
在asp.net中的调用
protected void Page_Load(object sender, EventArgs e)
{
DataBind();
}
private void DataBind()
{
//只带输入参数的存储过程的写法。
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
SqlCommand cmd = new SqlCommand("proc_user", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@name", "l%");
cmd.Parameters.AddWithValue("@Password", "lcs9702");
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
DropDownList1.DataSource = dr;
DropDownList1.DataTextField = "name";
DropDownList1.DataValueField = "use_id";
DropDownList1.DataBind();
}
private void textDataBind()
{
//带输入，输出参数的存储过程的写法。
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
SqlCommand cmd = new SqlCommand("proc_usertext", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@name", "love");
cmd.Parameters.Add("@blog", SqlDbType.VarChar,100);
cmd.Parameters["@blog"].Direction = ParameterDirection.Output;
//cmd.Parameters.Add(new SqlParameter("@blog",SqlDbType.VarChar,100,ParameterDirection.Output,false,0,0,string.Empty,DataRowVersion.Default,null));
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
TextBox1.Text = cmd.Parameters["@blog"].Value.ToString();
con.Close();
}
private void returnDataBind()
{
//带输入，返回参数的存储过程的写法。
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString);
SqlCommand cmd = new SqlCommand("returnval", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@name", "lcs9702");
cmd.Parameters.Add(new SqlParameter("@blog", SqlDbType.VarChar, 100, ParameterDirection.Output, false, 0, 0, string.Empty, DataRowVersion.Default, null));
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
TextBox2.Text = cmd.Parameters["@blog"].Value.ToString();
con.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
textDataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
returnDataBind();
}