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))
)
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, @des, @pwd, @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
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
--建立登录存储过程,验证用户名和密码
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
C#中调用存储过程方法见
后一篇。
存储过程的基本用法
最新推荐文章于 2022-09-02 16:23:22 发布