--创建测试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