create procedure 存储过程名
(
@sta_uid varchar,//用户标识
@sta_pwd varchar,//用户密码
@sta_name nvarchar output,
@Flag int output
)
as
set @Flag=-1
if(not exists select * from staffer where sta_uid=@sta_uid)
begin
set @Flag=3
return
end
if( exists select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd<>stapwd))
begin
set @Flag=2
return
end
if( exists select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd=stapwd)and(sta_state=0))
begin set @Flag=1
return
end
if( exists select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd=stapwd)and(sta_state=1))
begin
begin tran
select @sta_name=sta_name from staffer where (sta_uid=@sta_uid)and(@sta_pwd=stapwd)and(sta_state=1))
update staffer
set sta_=sta_+10
if(@@rowcount<>1)
begin
rollback tran
set @Flag=-1
return
end
set @Flag=0
commit tran
return
end
===============================
程序调用
申明2个入口参数,并复职,2个出口参数,
exec 存储过程名 @sta_uid ,@sta_pwd,@sta_name out,@Flag out
(
@sta_uid varchar,//用户标识
@sta_pwd varchar,//用户密码
@sta_name nvarchar output,
@Flag int output
)
as
set @Flag=-1
if(not exists select * from staffer where sta_uid=@sta_uid)
begin
set @Flag=3
return
end
if( exists select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd<>stapwd))
begin
set @Flag=2
return
end
if( exists select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd=stapwd)and(sta_state=0))
begin set @Flag=1
return
end
if( exists select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd=stapwd)and(sta_state=1))
begin
begin tran
select @sta_name=sta_name from staffer where (sta_uid=@sta_uid)and(@sta_pwd=stapwd)and(sta_state=1))
update staffer
set sta_=sta_+10
if(@@rowcount<>1)
begin
rollback tran
set @Flag=-1
return
end
set @Flag=0
commit tran
return
end
===============================
程序调用
申明2个入口参数,并复职,2个出口参数,
exec 存储过程名 @sta_uid ,@sta_pwd,@sta_name out,@Flag out
cmd.CommandType = CommandType.StoredProcedure;SqlParameter Parms;
Parms = cmd.Parameters .Add (new SqlParameter ("@参数",SqlDbType.Int));
Parms.Direction = ParameterDirection.Input;
Parms.Value = 树值;
...................
cmd.ExecuteNonQuery ();
return count;
//有一点点语法问题,我做了如下修改,没有语法错误了,但是在查询分析器并不能执行才存储过程,返回如下提示的错误:
----------------
服务器: 消息 201,级别 16,状态 4,过程 tempss,行 0
过程 'tempss' 需要参数 '@sta_name',但未提供该参数。
----------------------
明明是有的呀??这是怎么一回事呢??谢谢!!
***********************************************************
我修改为以适应我的数据库:
use myDB
if object_id('tempss') is not null
drop procedure tempss
gocreate procedure tempss
(
@sta_uid varchar,--用户标识
@sta_pwd varchar,--用户密码
@sta_name nvarchar output,
@Flag int output
)
as
set @Flag=-1
if (not exists (select * from staffer where sta_uid=@sta_uid))--此处修改
begin
set @Flag=3
return
end
if( exists (select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd<>sta_pwd)))--此处修改
begin
set @Flag=2
return
end
if( exists (select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd=sta_pwd)and(sta_state=0))) --此处修改
begin set @Flag=1
return
end
if( exists (select * from staffer where (sta_uid=@sta_uid)and(@sta_pwd=sta_pwd)and(sta_state=1))) --此处修改
begin
begin tran
select @sta_name=sta_name from staffer where (sta_uid=@sta_uid)and(@sta_pwd=sta_pwd)and(sta_state=1)
update staffer
set sta_=sta_+10
if(@@rowcount<>1)
begin
rollback tran
set @Flag=-1
return
end
set @Flag=0
commit tran
return
end
GO --修改exec tempss 'test','123456' --执行