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
jturkingtonAsked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
If I understand your problem correctly you are having issues with the jobref parameter.

try the following WHERE clause

WHERE     status = 'Live'
          AND     consultant = @consultant
          AND        job_title LIKE ('%'+@jobtitle +'%')
          AND (@jobref IS NULL or job_reference = @jobref)  --this will limit the results to a single job_reference if one is provided.  If you want to ignore the other parameters in this case then some other adjustments will be necessary.  Please clarify if this doesn't solve your problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.