Link to home
Start Free TrialLog in
Avatar of jturkington
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
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial