1.MemberCountByLetter
ALTER PROCEDURE MemberCountByLetter
AS
DECLARE @letter char(1), @index int, @max int
CREATE TABLE #results ( letter CHAR(1), num INT)
SET @index=ASCII('A')
SET @max=ASCII('Z')
WHILE @index <= @max
BEGIN
SET @letter = CHAR(@index)
INSERT INTO #results Select letter=@letter, num=count(*) from MemberInfo where SUBSTRING(lastname,1,1)=@letter
SET @index = @index + 1
END
Select * FROM #results
RETURN
AS
DECLARE @letter char(1), @index int, @max int
CREATE TABLE #results ( letter CHAR(1), num INT)
SET @index=ASCII('A')
SET @max=ASCII('Z')
WHILE @index <= @max
BEGIN
SET @letter = CHAR(@index)
INSERT INTO #results Select letter=@letter, num=count(*) from MemberInfo where SUBSTRING(lastname,1,1)=@letter
SET @index = @index + 1
END
Select * FROM #results
RETURN
2.NextPrevAnnouncement
ALTER PROCEDURE NextPrevAnnouncement
(
@id int,
@previd int output,
@nextid int output
)
AS
/* SET NOCOUNT ON */
DECLARE @curr datetime
SELECT @curr = itemdate
FROM Announcements
WHERE (id = @id)
SELECT @previd= id
FROM Announcements
WHERE (itemdate < @curr OR
(itemdate = @curr) AND (id < @id)) order by itemdate asc, id asc
SELECT @nextid= id
FROM Announcements
WHERE (itemdate > @curr OR
(itemdate = @curr) AND (id > @id)) order by itemdate desc, id desc
3.PagedAnnouncementList
ALTER PROCEDURE PagedAnnouncementList
(
@pageNum INT = 1,
@pageSize INT = 10
)
AS
DECLARE @rows INT
DECLARE @keydate DATETIME
DECLARE @keyid INT
DECLARE @rowCount FLOAT /* 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
SET ROWCOUNT @rows
SELECT @keydate=itemdate, @keyid=id FROM announcements ORDER BY itemdate ASC, id ASC
END
SELECT @rowCount=COUNT(*) FROM announcements
SET ROWCOUNT @pageSize
SELECT id, itemdate, title, description, photo FROM Announcements
WHERE (itemdate > @keydate OR
(itemdate = @keydate) AND (id > @keyid))
ORDER BY itemdate ASC, id ASC
RETURN CEILING(@rowCount/@pageSize)