--准备数据库createdatabase sample_db
use sample_db
--创建数据表createtable books (
book_id intidentity(1,1)primarykey,
book_name varchar(20),
book_price float,
book_auth varchar(10));--准备数据insertinto books (book_name,book_price,book_auth)values('论语',25.6,'孔子'),('天龙八部',25.6,'金庸'),('雪山飞狐',32.7,'金庸'),('平凡的世界',35.8,'路遥'),('史记',54.8,'司马迁');--创建存储过程--1、无参数存储过程if(exists(select*from sys.objects where name='getAllBooks'))dropproc getAllBooks
go
createproc getAllBooks
asselect*from books
exec getAllBooks
--修改存储过程alterproc getAllBooks
asselect book_name from books
--重命名存储过程
sp_rename getAllBooks,getBookName
exec getBookName
--删除存储过程dropproc getBookName
--创建带参数的存储过程--2、带一个参数if(exists(select*from sys.objects where name='searchBook'))dropproc searchBook
go
createproc searchBook(@bookIDint)asselect*from books where book_id=@bookIDexec searchBook 1--3、带两个参数if(exists(select*from sys.objects where name='twoParams'))dropproc twoParams
go
createproc twoParams(@bookIDint,@book_authvarchar(20))asselect*from books where book_id=@bookIDand book_auth=@book_authexec twoParams 1,'孔子'--4、创建有返回值的存储过程if(exists(select*from sys.objects where name ='getBookId'))dropproc getBookId
go
createproc getBookId(@bookAuthvarchar(20),--输入参数,无默认值@bookIdint output --输入/输出参数 无默认值)asselect@bookId=book_id from books where book_auth=@bookAuth--执行getBookId这个带返回值的存储过程declare@idint--声明一个变量用来接收执行存储过程后的返回值exec getBookId '孔子',@id output
select@idas bookId;--as是给返回的列值起一个名字--5、创建带有通配符的存储过程if(exists(select*from sys.objects where name ='charBooks'))dropproc charBooks
go
createproc charBooks(@bookAuthvarchar(20)='金%',@bookNamevarchar(20)='%')asselect*from books where book_auth like@bookAuthand book_name like@bookName;--执行存储过程charBooksexec charBooks '孔%','论%';exec sp_helptext 'charBooks'--6、加密存储过程if(object_id('books_encryption','P')isnotnull)dropproc books_encryption
go
createproc books_encryption
with encryption
asselect*from books;--执行此过程books_encryptionexec books_encryption;exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"--7、不缓存存储过程--with recompile不缓存if(object_id('book_temp','P')isnotnull)dropproc book_temp
go
createproc book_temp
with recompile
asselect*from books;
go
exec book_temp;exec sp_helptext 'book_temp';--8、创建带游标参数的存储过程if(object_id('book_cursor','P')isnotnull)dropproc book_cursor
go
createproc book_cursor
@bookCursorcursorvarying output
asset@bookCursor=cursor forward_only static forselect book_id,book_name,book_auth from books
open@bookCursor;
go
--调用book_cursor存储过程declare@curcursor,@bookIDint,@bookNamevarchar(20),@bookAuthvarchar(20);exec book_cursor @bookCursor=@cur output;fetchnextfrom@curinto@bookID,@bookName,@bookAuth;while(@@FETCH_STATUS=0)beginfetchnextfrom@curinto@bookID,@bookName,@bookAuth;print'bookID:'+convert(varchar,@bookID)+' , bookName: '+@bookName+' ,bookAuth: '+@bookAuth;endclose@cur--关闭游标DEALLOCATE@cur;--释放游标--9、创建分页存储过程if(object_id('book_page','P')isnotnull)dropproc book_page
go
createproc book_page(@TableNamevarchar(50),--表名@ReFieldsStrvarchar(200)='*',--字段名(全部字段为*)@OrderStringvarchar(200),--排序字段(必须!支持多字段不用加order by)@WhereStringvarchar(500)=N'',--条件语句(不用加where)@PageSizeint,--每页多少条记录@PageIndexint=1,--指定当前为第几页@TotalRecordint output --返回总记录数)asbegin--处理开始点和结束点Declare@StartRecordint;Declare@EndRecordint;Declare@TotalCountSql nvarchar(500);Declare@SqlString nvarchar(2000);set@StartRecord=(@PageIndex-1)*@PageSize+1set@EndRecord=@StartRecord+@PageSize-1SET@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)BEGINSET@TotalCountSql=@TotalCountSql+' where '+@WhereString;SET@SqlString=@SqlString+' where '+@WhereString;END--第一次执行得到--IF(@TotalRecord is null)-- BEGINEXEC 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_pageexec book_page 'books','*','book_id','',3,1,0;--declare@totalCountintexec book_page 'books','*','book_id','',3,1,@totalCount output;select@totalCountas totalCount;--总记录数。