ALTER PROCEDURE Users_List
@username VARCHAR(50) = NULL ,
@firstname VARCHAR(50) = NULL ,
@lastname VARCHAR(100) = NULL ,
@usertype INT=0,
@sortcolumn VARCHAR(100)= NULL,
@siteid INT


AS

SET @username = ltrim(rtrim(@username))
SET @firstname = ltrim(rtrim(@firstname))
SET @lastname = ltrim(rtrim(@lastname))
SET @sortcolumn = ltrim(rtrim(@sortcolumn))

IF @username = ''
SET @username = null

IF @firstname = ''
SET @firstname = null

IF @lastname = ''
SET @lastname = null

IF @sortcolumn = ''
SET @sortcolumn = null

IF @usertype = 0
SET @usertype = null





SELECT users.id , users.firstname,users.lastname,users.type,usertypes.name,users.username,users.agentid,users.password,users.status
FROM users,usertypes
WHERE (@username IS null OR ltrim(users.username) like @username + '%' )
AND (@firstname IS null OR ltrim(users.firstname) like @firstname + '%' )
AND (@lastname IS null OR ltrim(users.lastname) like @lastname + '%' )
AND (@usertype IS null OR users.type = @usertype )
AND status >-1
AND users.type = usertypes.id
AND users.siteid = @siteid
ORDER BY
CASE WHEN @sortcolumn = 'firstname' THEN users.firstname END,
CASE WHEN @sortcolumn = 'lastname' THEN users.lastname END,
CASE WHEN @sortcolumn = 'username' THEN users.username END,
CASE WHEN @sortcolumn = 'type' THEN users.type END