用户自定义函数例题.docVIP

用户自定义函数例题（填空） set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo ALTER FUNCTION [dbo].[fnProList](?@parid?int, @level?int)RETURNS?@proList TABLE?(?proId??int,?proname??varchar(50),?proparid?int,?Sonsum??int,?curlevel?int,?orderno??int identity(1, 1))ASBEGIN?declare???@proId??int,??@proname?varchar(50),??@sonsum??int?declare curItem cursor for??select proId, proname, sonsum???from product???where proparid = @parid?open curItem?fetch next from curItem into @proId, @proname, @sonsum?while @@fetch_status = 0?begin??Insert into @proList(proid, proname, proparid, sonsum, curlevel)???values(@proId, @proname, @parid, @sonsum, @level)??if @sonsum 0??begin???set @level = @level + 1???Insert into @proList(proid, proname, proparid, sonsum, curlevel)????select proid, proname, proparid, sonsum, curlevel?????from fnProList(@proid, @level)???set @level = @level - 1??end??fetch next from curItem into @proId, @proname, @sonsum?end?close curItem?deallocate curItem??RETURN?END 存储过程例题（大题目） ALTER PROCEDURE ap_GetPage?@cID??int,?@pageNum?int,?-- 每页显示的回复数?@pageNo??int??-- 第几页ASBEGIN?SET NOCOUNT ON;?declare??@totalPages??int,??@count???int?select @count = Count(*)???from recontent??where cID = @cId?set @totalPages = @count/@pageNum + 1?if @pageNo 1 or @pageNo @totalPages??return -1?if @pageNo = 1??select top (@pageNum -1) *???from reContent???where cID = @cID???order by reID??else?begin??declare???@minReID??int??select @minReID = max(reID)???from (????select top ((@pageNo -1)*@pageNum-1) reID?????from recontent?????where cID = @cID?????order by reID???) t??select top (@pageNum) *???from reContent???where cID = @cID and reID @minReID???order by reID??end?return @totalPagesENDGO