例一:
存储过程:(通过 用户ID 输出 用户名)
create proc test
(
@FUid int,
@FUserName varchar(100) output
)
as
begin
select @FUserName=FUserName from B_User where Fid = @FUid
end
go
*****在一个区域内如果有多条语句时,必需使用BEGIN...END关键字。
asp.net测试程序:
public void test()
{
SqlConnection sqlcon = new SqlConnection(CnnString);
sqlcon.Open();
SqlCommand sqlcom = new SqlCommand();
sqlcom.CommandType = CommandType.StoredProcedure;
sqlcom.CommandText = "test";
sqlcom.Connection = sqlcon;
sqlcom.Parameters.Add("@FUid", SqlDbType.Int, 2);
sqlcom.Parameters["@FUid"].Value = "2";
sqlcom.Parameters.Add("@FUserName", SqlDbType.VarChar, 500);
sqlcom.Parameters["@FUserName"].Direction = ParameterDirection.Output;
sqlcom.ExecuteNonQuery();(); //一定先执行,才可以获得@FUserName值
sqlcon.Close();
string name = sqlcom.Parameters["@FUserName"].Value.ToString(); //获取output输出变量的值
}
例二:
存储过程:
create proc test2
(
@FUserName varchar(100),
@FPwd varchar(100),
@returnBoll bit output
)
as
declare @strPwd varchar(100) --定义一个临时变量来保存密码.
begin
--通过用户名查询用户密码,并将此密码赋给临时变量 @strPwd
select @strPwd = FPwd from B_User where FUserName = @FUserName
--判断输入的密码与查询到的密码是否相等.
--如果相等,设置输出的值为1,并且更新最后一次登录的时间为系统当前时间;如果不等,设置输出的值为0.
if(@FPwd = @strPwd)
begin
set @returnBoll = 1
update B_User set FLastLoginTime = getdate() where FUserName = @FUserName
end
else
begin
set @returnBoll = 0
end
end
go
asp.net测试程序:
public void test()
{
SqlConnection sqlcon = new SqlConnection(CnnString);
sqlcon.Open();
SqlCommand sqlcom = new SqlCommand();
sqlcom.CommandType = CommandType.StoredProcedure;
sqlcom.CommandText = "test2";
sqlcom.Connection = sqlcon;
sqlcom.Parameters.Add("@FUserName", SqlDbType.VarChar, 100);
sqlcom.Parameters["@FUserName"].Value = "bbb";
sqlcom.Parameters.Add("@FPwd", SqlDbType.VarChar,100);
sqlcom.Parameters["@FPwd"].Value = "EA-ED-67-D6-AE-E3-43-90";
sqlcom.Parameters.Add("@returnBoll", SqlDbType.Bit);
sqlcom.Parameters["@returnBoll"].Direction = ParameterDirection.Output;
sqlcom.ExecuteNonQuery();
sqlcon.Close();
bool name = Convert.ToBoolean(sqlcom.Parameters["@returnBoll"].Value);
if (name == true)
{
Response.Write("登录成功!");
}
else
{
Response.Write("不是吧?好像错了哦!登录不了.....");
}
}