你所在位置:首页Sql资源 → 存储过程的基本用法

存储过程的基本用法

发布时间:2019-05-15

 

[sql]

if db_id('testProcedure') is not null   

begin  

    use master  

    drop database testProcedure  

end  

  

create database testProcedure  

go  

use testProcedure  

  

create table Users  

(  

    ID bigint identity primary key,  

    UserName nvarchar (20) not null unique,  

    Description nvarchar (20),  

    Password varchar(20),  

    Authority tinyint check (Authority in (0,1,2))  

)    www.350job.com  

go  

--定义查询所有用户的存储过程  

create proc spSelectAll as  

    select * from Users  

go  

  

--调用存储过程  
 

exec spSelectAll;  

execute spSelectAll;  

exec sp_helptext spSelectAll;--查看定义语句  

select [text] from syscomments where id =(  

    select Id from sysobjects where name='spSelectAll')--查看定义语句   

go  

  

--定义插入某用户的存储过程  
 

create proc spInsertUser(@un nvarchar(20), @pwd varchar(20), @des nvarchar(20), @au tinyint=2) as  

begin --定义体中有多条语句,建议用begin……end构成语句块  

    if exists(select * from Users where username = @un) return  

    insert into Users values(@un, @pwd, @des, @au)  

end  

go  

  

--调用存储过程  

exec spInsertUser 'cshlj','123','一般用户',1;  

exec spInsertUser 'ishlj','123','管理员',0;  

exec spInsertUser 'guesthlj','123','过客匆匆';  

go  

  

--定义修改密码的存储过程  

create proc spUpdatePassWord(@un nvarchar(20), @pwd varchar(20)) as  

begin    www.350job.com  

    update Users set Password=@pwd where username = @un  

    return @@rowcount --返回上一语句影响的行数,可用于判断是否修改成功  

end  

go  

  

--调用存储过程  

exec spUpdatePassWord 'cshlj', '123456'  

  

go  

  

--定义删除某用户的存储过程  
 

create proc spDeleteUser(@un nvarchar(20)) as  

    delete from Users where username = @un  

go  

  

--调用存储过程  

exec spDeleteUser 'guesthlj'  

  

go  

--定义查询按用户名模糊查询的存储过程  
 

create proc spSelectByUsername(@un nvarchar(20)) as  

    select * from Users where username like '%'+ @un +'%'  

go  

  

--调用存储过程  
 

exec spSelectByUsername @un='cs';  

execute spSelectByUsername 'cs';  

go  

  

--定义查询按用户名和权限查询的存储过程  
 

create proc spGetOneUser(@un nvarchar(20)='cshlj', @au tinyint=0) as  

    select * from Users where username = @un and Authority=@au  

go  

  

--调用存储过程  
 

exec spGetOneUser; --参数都使用默认值  

exec spGetOneUser 'ishlj'; --第二个参数使用默认值  

exec spGetOneUser 'ishlj', 1; --参数都不使用默认值  

exec spGetOneUser @un='ishlj', @au=1; --指定参数名  

exec spGetOneUser @au=1, @un='ishlj';--指定参数名时,参数顺序可以与定义时的顺序不一致  

go    www.350job.com  

  

--建立登录存储过程,验证用户名和密码  
 

create proc spLogin(@user nvarchar(20),@pass varchar(20),@auth tinyint output)  

with encryption--带选项,避免定义语句被查看  

as  

begin  

    if(select Count(*) from Users where UserName=@user and password=@pass)=0  

        return 0   

    else  

    begin     

        select @auth = [authority] from Users where UserName=@user  

        return 1  

    end  

end  

  

go  

  

declare @res int, @au tinyint  

exec @res=spLogin 'cshlj', '123456', @au output; --输出参数必须带output  

select @res as 返回值, @au as 权限 --显示查询结果  

  

go  

  

--修改存储过程的定义  

alter proc spLogin(@user nvarchar(20),@pass varchar(20),@auth tinyint output)  

as  

begin     

    select @auth = [authority] from Users where UserName=@user and password=@pass  

    if @auth is null return 0     

    return 1  

end  

  

go  

--删除存储过程  

drop proc spLogin  

 


 

作者 acmerhlj


上一篇:SQL Server“标识列”的相关问题
下一篇:MS Sql Server中存储过程与触发器的安全隐患测试