Still wasn't satisfied with the procedure because i couldn't sort... made another change. Also learned that ORDER BY @param will pass syntax checking, won't error when executed either, but doesn't work.
ALTER PROCEDURE [dbo].[GetPersonContactByName]
(
@length int,
@page int,
@firstname nvarchar(50),
@lastname nvarchar(50),
@orderby nvarchar(50)
)
AS
declare @totalrows float
set @totalrows = ( SELECT COUNT(*) from Person.Contact where LastName like (@lastname) and FirstName like (@firstname));
declare @lastrow int
set @lastrow = (@page - 1) * @length
declare @lastpage int
set @lastpage = CEILING(@totalrows / @length)
if (@page > @lastpage) set @page = @lastpage
if(@page < 1) set @page = 1;
/* SET NOCOUNT ON */
WITH Ordered AS
(SELECT ROW_NUMBER()
OVER
(
ORDER BY CASE
WHEN @orderby = N'EmailAddress' THEN EmailAddress
WHEN @orderby = N'FirstName' THEN FirstName
WHEN @orderby = N'Title' THEN Title
WHEN @orderby = N'MiddleName' THEN MiddleName
WHEN @orderby = N'Suffix' THEN Suffix
WHEN @orderby = N'Phone' THEN Phone
ELSE LastName
END,
CASE WHEN @orderby = N'EmailAddress' THEN LastName
WHEN @orderby = N'FirstName' THEN LastName
WHEN @orderby = N'Title' THEN LastName
WHEN @orderby = N'MiddleName' THEN LastName
WHEN @orderby = N'Suffix' THEN LastName
WHEN @orderby = N'Phone' THEN LastName
ELSE FirstName
END
)
AS
rownum, ContactID, NameStyle, Title, FirstName, MiddleName,
LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash,
PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate
FROM Person.Contact
WHERE LastName like (@lastname) and FirstName like (@firstname)
)
SELECT TOP (@length)
@page as Page, @lastpage as TotalPages, ContactID, NameStyle, Title, FirstName, MiddleName,
LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, AdditionalContactInfo, rowguid, ModifiedDate
FROM Ordered
WHERE rownum > @lastrow
return @totalrows
