-- 判断存储过程是否存在
if (exists (select * from sys.objects where name = 'getAllBooks'))
Drop Procedure getAllBooks
go
-- 创建存储过程
Create Procedure getAllBooks
as
-- SQL语句
Select * From books
修改存储过程
Alter Procedure getAllBooks
as
-- 修改后SQL语句
Select book_name From books
删除存储过程
-- 删除存储过程
drop Procedure getAllBooks
重命名存储过程
sp_rename getAllBooks,proc_get_allBooks
有参存储过程
参数分类
输入参数,用于向存储过程传入值;
输出参数,用于调用存储过程后,返回结果;
值传递和引用传递
基本数据类型赋值属于值传递;引用类型之间赋值属于引用传递;
值传递传递的是变量值;引用传递传递的事对象的引用地址;
值传递后,两个变量改变的是各自的值;引用传递后,两个引用改变的是同一个对象的状态;
单个参数存储过程
if(exists (Select * From sys.objects Where name = 'searchBooks'))
Drop Procedure searchBooks
go
Create Proc searchBooks(
-- 参数声明(@参数名 类型 [=默认值] )
@bookID int
)
as
Select * From books
Where book_id = @bookID
-- 调用存储过程
exec searchBooks 1;
多个参数存储过程(两个参数)
if(exists (Select * From sys.objects Where name = 'searchBooks'))
Drop Procedure searchBooks
go
Create Proc searchBooks(
@bookID int ,
@auth nvarchar(50)
)
as
Select * From books
Where book_id = @bookID
And book_auth = @auth
-- 调用存储过程
exec searchBooks 1 , '孔子'
带返回值的存储过程
if (exists (Select * From sys.objects Where name = 'getBookId'))
Drop Procedure getBookId
go
Create Procedure getBookId(
@bookauth nvarchar(20), --无默认值
@bookId int output --无默认值
)
as
Select @bookId = book_Id From books
Where book_auth = @bookauth
--调用存储过程
declare @Id int
exec getBookId '金庸',@Id output
select @id as booksId
参数带通配符的存储过程(参数有默认值)
if (exists (select * from sys.objects where name = 'charBooks'))
drop proc charBooks
go
create proc charBooks(
@bookAuth varchar(20)='金%',--默认值
@bookName varchar(20)='%'--默认值
)
as
select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程
exec charBooks '孔%','论%';