SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      brandner
-- Description: get all users belonging to site.
-- =============================================

ALTER PROCEDURE [dbo].[usp_searchUsers]
    -- Add the parameters for the stored procedure here
    @siteID int,
    @startRow int,
    @maxRows int,
    @searchParam varchar(255),
    @sortBy varchar(255),
    @sortDirection varchar(4)

AS
BEGIN

DECLARE @endRow int

SELECT @endRow = @startRow + @maxRows - 1

SELECT @searchParam = CASE WHEN @searchParam = '' or @searchParam is null THEN '%' ELSE @searchParam END

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

WITH siteUsers AS

    (
    SELECT p.personID, p.username,
        n.givenName, n.familyName,
        -- more user related columns here
        -- use SQL Server 2k5 row_number functionality to allow sorting
        ROW_NUMBER() OVER (order by p.username asc) as sortUsername,
        ROW_NUMBER() OVER (order by p.username desc) as sortUsernameDESC,
        ROW_NUMBER() OVER (order by n.familyName asc) as sortFamilyName,
        ROW_NUMBER() OVER (order by n.familyName desc) as sortFamilyNameDESC

    FROM person_info AS p
    LEFT OUTER JOIN name AS n ON n.personID = p.personID
    -- more joins here for user data

    -- cross join on a table that contains one row per search keyword
    CROSS JOIN dbo.listToTable(@searchParam,' ') params
    WHERE p.siteID = @siteID
        -- dont pull accounts marked as invalid
        -- filter to find ANY keywords that match
        AND (
            [n].[familyName] like '%' + params.value + '%'
            OR [n].[givenName] like '%' + params.value + '%'
            OR [p].[userName] like '%' + params.value + '%'
            -- more filters here as necessary
        )

    -- group rows so that we only have one record per user and can use row_number functionality
    GROUP BY p.personID, n.familyName, p.userName, n.givenname

    -- limit to records that had at lease one match for each keyword
    HAVING count(*) = ( select count(*) as paramCount from dbo.listToTable(@searchParam,' ') )
)

SELECT *, (select count(*) from siteUsers) as 'siteUsersCount',
FROM siteUsers
WHERE
    case @sortDirection
    when 'asc' then
        case @sortBy
            when 'familyName' then sortFamilyName
            when 'username' then sortUsername
        end
    else
        case @sortBy
            when 'familyName' then sortFamilyNameDESC
            when 'username' then sortUsernameDESC
        end
    end between @startRow and @endRow
ORDER BY
    case @sortDirection
    when 'asc' then
        case @sortBy
            when 'familyName' then sortFamilyName
            when 'username' then sortUsername
        end
    else
        case @sortBy
            when 'familyName' then sortFamilyNameDESC
            when 'username' then sortUsernameDESC
        end
    end
end