????(6).??????洢????
????--with recompile??????
????if (object_id('book_temp'?? 'P') is not null)
????drop proc book_temp
????go
????create proc book_temp
????with recompile
????as
????select * from books;
????go
????exec book_temp;
????exec sp_helptext 'book_temp';
????(7).???????α??????洢????
if (object_id('book_cursor'?? 'P') is not null)
drop proc book_cursor
go
create 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 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; --????α?
????(8).????????洢????
if (object_id('book_page'?? 'P') is not null)
drop proc book_page
go
create 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 book_page 'books'??'*'??'book_id'??''??3??1??0;
--
declare @totalCount int
exec book_page 'books'??'*'??'book_id'??''??3??1??@totalCount output;
select @totalCount as totalCount;--????????