ALTERPROCEDURE MemberCountByLetter AS DECLARE@letterchar(1), @indexint, @maxint CREATETABLE #results ( letter CHAR(1), num INT) SET@index=ASCII('A') SET@max=ASCII('Z') WHILE@index<=@max BEGIN SET@letter=CHAR(@index) INSERTINTO #results Select letter=@letter, num=count(*) from MemberInfo whereSUBSTRING(lastname,1,1)=@letter SET@index=@index+1 END Select*FROM #results RETURN
2.NextPrevAnnouncement
ALTERPROCEDURE NextPrevAnnouncement ( @idint, @previdint output, @nextidint output ) AS /**//* SET NOCOUNT ON */ DECLARE@currdatetime SELECT@curr= itemdate FROM Announcements WHERE (id =@id) SELECT@previd= id FROM Announcements WHERE (itemdate <@currOR (itemdate =@curr) AND (id <@id)) orderby itemdate asc, id asc SELECT@nextid= id FROM Announcements WHERE (itemdate >@currOR (itemdate =@curr) AND (id >@id)) orderby itemdate desc, id desc
3.PagedAnnouncementList
ALTERPROCEDURE PagedAnnouncementList ( @pageNumINT=1, @pageSizeINT=10 ) AS DECLARE@rowsINT DECLARE@keydateDATETIME DECLARE@keyidINT DECLARE@rowCountFLOAT/**//* yes we need a float for the math */ IF@pageNum=1 BEGIN SET@keydate=0 SET@keyid=0 END ELSE BEGIN /**//* get the values for the date and row */ SELECT@rows= (@pageNum-1) *@pageSize SETROWCOUNT@rows SELECT@keydate=itemdate, @keyid=id FROM announcements ORDERBY itemdate ASC, id ASC END SELECT@rowCount=COUNT(*) FROM announcements SETROWCOUNT@pageSize SELECT id, itemdate, title, description, photo FROM Announcements WHERE (itemdate >@keydateOR (itemdate =@keydate) AND (id >@keyid)) ORDERBY itemdate ASC, id ASC RETURNCEILING(@rowCount/@pageSize)
posted on
2006-09-07 22:56cobbles
阅读(201)
评论(0)
编辑收藏举报