存储过程自我练习 最基本的

//带参数的存储过程
create procedure p_2(@name varchar(20),@password varchar(20) output)
as
select @password=Password from userlist where Nickname=@name
//执行带参数的存储过程
declare @p varchar(20)
exec p_2 '李小花',@p output//按照参数的位置给 也可以按照参数名字给  [exec p_2 @name='李小花',@password=@p output]
select @p

declare @p varchar(20)
exec p_2 @name='李小花',@password=@p output
select @p as pass //给输出参数取别名
//多个输出参数
create procedure p_3(@name varchar(20),@password varchar(20) output,@description varchar(20) output,
@usertype int output,@addtime datetime output)
as
select @password=Password,@name=nickname,@description=description,@usertype=usertype,
@addtime=addtime from userlist where Nickname=@name
declare @p1 varchar(20)
declare @p2 varchar(20)
declare @p3 int
declare @p4 datetime
exec p_3 @name='李小花' ,@password=@p1 output ,@description=@p2 output ,@usertype=@p3 output ,@addtime=@p4 output
select @p1 as pass ,@p2 as description,@p3 as usertype,@p4 as addtime

//修改存储过程并且加密 加密了以后就不能看到存储的文本
alter procedure p_3(@name varchar(20),@password varchar(20) output,@description varchar(20) output,
@usertype int output,@addtime datetime output)
with encryption
as
select @password=Password,@name=nickname,@description=description,@usertype=usertype,
@addtime=addtime from userlist where Nickname=@name

//重命名
exec sp_rename p_3,p_lct
//查询存储文本 加密之后看不到存储过程的文本
exec sp_helptext p_lct
//给存储过程解密
alter procedure p_lct