--创建测试books表
create table books (
    book_id int identity(1,1) primary key,
    book_name varchar(20),
    book_price float,
    book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
                    values
                        (‘论语‘,25.6,‘孔子‘),
                        (‘天龙八部‘,25.6,‘金庸‘),
                        (‘雪山飞狐‘,32.7,‘金庸‘),
                        (‘平凡的世界‘,35.8,‘路遥‘),
                        (‘史记‘,54.8,‘司马迁‘);

--创建无参存储过程
if (exists (select * from sys.objects where name = ‘proc_getAllBooks‘))
    drop proc proc_getAllBooks
go
create procedure proc_getAllBooks
as
select * from books;
--调用,执行存储过程
exec proc_getAllBooks;

alter procedure dbo.proc_getAllBooks 
as
select book_auth from books

drop procedure dbo.proc_getAllBooks;

sp_rename proc_getAllBooks,proc_get_allBooks;

if (exists (select * from sys.objects where name = ‘proc_searchBooks‘))
    drop proc proc_searchBooks
go
create proc proc_searchBooks(@bookID int)
as
    --要求book_id列与输入参数相等
    select * from books where book_id=@bookID;

--执行proc_searchBooks
exec proc_searchBooks 1;

if (exists (select * from sys.objects where name = ‘proc_searchBooks1‘))
    drop proc searchBooks1
go
create proc proc_searchBooks1(
    @bookID int,
    @bookAuth varchar(20)
)
as
    --要求book_id和book_Auth列与输入参数相等
    select * from books where book_id=@bookID and book_auth=@bookAuth;

exec proc_searchBooks1 1,‘金庸‘;

if (exists (select * from sys.objects where name = ‘proc_getBookId‘))
    drop proc proc_getBookId
go
create proc proc_getBookId(
    @bookAuth varchar(20),--输入参数,无默认值
    @bookId int output --输入/输出参数 无默认值
)
as
    select @bookId=book_id from books where book_auth=@bookAuth

--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec proc_getBookId ‘孔子‘,@id output
select @id as bookId;--as是给返回的列值起一个名字

if (exists (select * from sys.objects where name = ‘proc_charBooks‘))
    drop proc proc_charBooks
go
create proc proc_charBooks(
    @bookAuth varchar(20)=‘%‘,
    @bookName varchar(20)=‘%‘
)
as 
    select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程proc_charBooks
exec  proc_charBooks    ‘孔%‘,‘论%‘;

if (object_id(‘proc_books_encryption‘, ‘P‘) is not null)
    drop proc proc_books_encryption
go
create proc proc_books_encryption 
with encryption
as 
    select * from books;
--执行此过程books_encryption
exec proc_books_encryption;
exec sp_helptext ‘proc_books_encryption‘;--控制台会显示"对象 ‘proc_books_encryption‘ 的文本已加密。"

--with  recompile不缓存
if (object_id(‘proc_book_temp‘, ‘P‘) is not null)
    drop proc proc_book_temp
go
create proc proc_book_temp
with recompile
as
    select * from books;
go

exec proc_book_temp;
exec sp_helptext ‘proc_book_temp‘;

if (object_id(‘proc_book_cursor‘, ‘P‘) is not null)
    drop proc proc_book_cursor
go
create proc proc_book_cursor
    @bookCursor cursor varying output
as
    set @bookCursor=cursor forward_only static for
    select book_id,book_name,book_auth from books
    open @bookCursor;
go

--调用book_cursor存储过程
declare @cur cursor,
        @bookID int,
        @bookName varchar(20),
        @bookAuth varchar(20);
exec proc_book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin 
    fetch next from @cur into @bookID,@bookName,@bookAuth;
    print ‘bookID:‘+convert(varchar,@bookID)+‘ , bookName: ‘+ @bookName
            +‘ ,bookAuth: ‘+@bookAuth;
end
close @cur    --关闭游标
DEALLOCATE @cur; --释放游标

if (object_id(‘proc_book_page‘, ‘P‘) is not null)
    drop proc proc_book_page
go
create proc proc_book_page(
    @TableName varchar(50),            --表名
    @ReFieldsStr varchar(200) = ‘*‘,   --字段名(全部字段为*)
    @OrderString varchar(200),         --排序字段(必须!支持多字段不用加order by)
    @WhereString varchar(500) =N‘‘,  --条件语句(不用加where)
    @PageSize int,                     --每页多少条记录
    @PageIndex int = 1 ,               --指定当前为第几页
    @TotalRecord int output            --返回总记录数
)
as
begin
     --处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N‘select @TotalRecord = count(*) from ‘ + @TableName;--总记录数语句
    SET @SqlString = N‘(select row_number() over (order by ‘+ @OrderString +‘) as rowId,‘+@ReFieldsStr+‘ from ‘+ @TableName;--查询语句
    --
    IF (@WhereString! = ‘‘ or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + ‘  where ‘+ @WhereString;
            SET @SqlString =@SqlString+ ‘  where ‘+ @WhereString;            
        END
    --第一次执行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N‘@TotalRecord int out‘,@TotalRecord output;--返回总记录数
    --  END
    ----执行主语句
    set @SqlString =‘select * from ‘ + @SqlString + ‘) as t where rowId between ‘ + ltrim(str(@StartRecord)) + ‘ and ‘ +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
END

--调用分页存储过程book_page
exec proc_book_page ‘books‘,‘*‘,‘book_id‘,‘‘,3,1,0;

--
declare @totalCount int
exec proc_book_page ‘books‘,‘*‘,‘book_id‘,‘‘,3,1,@totalCount output; 
select @totalCount as totalCount;--总记录数。