--创建测试books表
create table books_test (
book_id int identity(1,1) primary key,
book_name varchar(20),
book_price float,
book_auth varchar(10)
);
--------插入测试数据
INSERT INTO books_test (book_name,book_price,book_auth)
VALUES
('论语',25.6,'孔子'),
('天龙八部',25.6,'金庸'),
('雪山飞狐',32.7,'金庸'),
('平凡的世界',35.8,'路遥'),
('史记',54.8,'司马迁');
--查看一下
SELECT * FROM books_test
---创建无参存储过程
IF(EXISTS(SELECT * FROM sys.objects WHERE name='getAllBooks'))
DROP PROC getAllBooks
GO
CREATE PROCEDURE getAllBooks
AS
BEGIN
SELECT * FROM books_test;
END
--执行无参存储过程
EXEC getAllBooks
--修改存储过程
ALTER procedure getAllBooks
AS
SELECT book_name FROM books_test
--删除存储过程
DROP PROCEDURE getAllBooks;
--修改存储过程的名称
sp_rename getAllBooks,proc_get_allBooks
go
EXEC Proc_get_allBooks
go
--创建带参数的存储过程
IF(EXISTS (SELECT * FROM sys.objects WHERE name='searchBooks'))
DROP PROC searchbooks
go
CREATE PROCEDURE searchBooks
@bookid int
AS
BEGIN
DECLARE @book_id INT
SET @book_id=@bookid;
SELECT * FROM books_test WHERE book_id=@book_id;
END
GO
--执行带参数的存储过程
EXEC searchBooks 1
--创建带返回值的存储过程
IF(exists(SELECT * FROM sys.objects WHERE name='book_test3'))
DROP PROC book_test3
go
CREATE PROCEDURE book_test3(@bookid INT ,@out_book_name VARCHAR(20) OUTPUT)
AS
DECLARE @book_id INT;
SET @book_id=@bookid;
BEGIN
SELECT @out_book_name=book_name FROM books_test WHERE book_id=@book_id;
END
go
--执行带参数的存储过程
DECLARE @out_name VARCHAR(20)
EXEC book_test3 1,@out_name OUTPUT
--SELECT @out_name AS out_book_name;
PRINT @out_name
