存储过程的基本用法

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#中调用存储过程方法见后一篇。