jturkington
asked on
Search Interface SPROC Design Question
I have the following layout for a search form: -
JobRef [TEXTBOX] JobTitle[TEXTBOX] Consultant[SELECT BOX]
<option value="" selected="selected">All Consultants</option>
<option value="john">John</option>
Trying To Achieve The Following: -
1. Always Return records with STATUS = 'Live'
2. If Job Ref is input only one record is returned or none even with other combinations (JobTitle Or Consultant)
3. All other combinations are possible
JobTitle: - job_title LIKE ('%'+@jobtitle +'%')
Consultant: - consultant = @consultant
If All Consultants are selected it will return all consultants records and filter if job title is inputted etc..
Query so far: -
CREATE PROCEDURE spSelect_Jobs_On_Search
@jobref INT,
@jobtitle VARCHAR(100),
@consultant VARCHAR(50),
@SortBy VARCHAR(20),
@SortAscending CHAR(1)
As
-- Select Consultants For DropDown Box
SELECT username,
firstname,
lastname
FROM Users
WHERE roles = 'Consultant'
ORDER BY firstname
SET NOCOUNT ON
IF @SortAscending = 'A'
BEGIN
SELECT job_reference,
job_title,
dateadded,
status,
consultant
FROM Jobs
WHERE status = 'Live'
AND consultant = @consultant
AND job_title LIKE ('%'+@jobtitle +'%')
ORDER BY
CASE
WHEN @SortBy = 'jobref' Then job_reference
WHEN @SortBy = 'dateadded' Then dateadded
END
ASC
END
ELSE
BEGIN
SELECT job_reference,
job_title,
dateadded,
status,
consultant
FROM Jobs
WHERE status = 'Live'
AND consultant = @consultant
AND job_title LIKE ('%'+@jobtitle +'%')
ORDER BY
CASE
WHEN @SortBy = 'jobref' Then job_reference
WHEN @SortBy = 'dateadded' Then dateadded
END
DESC
END
HELP !?
JT
JobRef [TEXTBOX] JobTitle[TEXTBOX] Consultant[SELECT BOX]
<option value="" selected="selected">All Consultants</option>
<option value="john">John</option>
Trying To Achieve The Following: -
1. Always Return records with STATUS = 'Live'
2. If Job Ref is input only one record is returned or none even with other combinations (JobTitle Or Consultant)
3. All other combinations are possible
JobTitle: - job_title LIKE ('%'+@jobtitle +'%')
Consultant: - consultant = @consultant
If All Consultants are selected it will return all consultants records and filter if job title is inputted etc..
Query so far: -
CREATE PROCEDURE spSelect_Jobs_On_Search
@jobref INT,
@jobtitle VARCHAR(100),
@consultant VARCHAR(50),
@SortBy VARCHAR(20),
@SortAscending CHAR(1)
As
-- Select Consultants For DropDown Box
SELECT username,
firstname,
lastname
FROM Users
WHERE roles = 'Consultant'
ORDER BY firstname
SET NOCOUNT ON
IF @SortAscending = 'A'
BEGIN
SELECT job_reference,
job_title,
dateadded,
status,
consultant
FROM Jobs
WHERE status = 'Live'
AND consultant = @consultant
AND job_title LIKE ('%'+@jobtitle +'%')
ORDER BY
CASE
WHEN @SortBy = 'jobref' Then job_reference
WHEN @SortBy = 'dateadded' Then dateadded
END
ASC
END
ELSE
BEGIN
SELECT job_reference,
job_title,
dateadded,
status,
consultant
FROM Jobs
WHERE status = 'Live'
AND consultant = @consultant
AND job_title LIKE ('%'+@jobtitle +'%')
ORDER BY
CASE
WHEN @SortBy = 'jobref' Then job_reference
WHEN @SortBy = 'dateadded' Then dateadded
END
DESC
END
HELP !?
JT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.