[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Search Interface SPROC Design Question

Posted on 2005-05-06
1
Medium Priority
?
167 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:jturkington
1 Comment
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 13944919
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question