Challenge Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Challenge Query

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-12-30 : 12:21:18

Well Ladies & Gentlements:

I have one of the most complex queries you can imagine. I need to filter out by 4 selections. The user have the choice to select 1 selection or all 4 selections.
Here is an example of my querie. It is not working out very well. I can select * From the multiple Tables by each individual queries or by each selection. But I am having problems figuer it out how to query out by multiple selection at the same time.



stored procedure Name blablablaba
-----------------------------------------
@SQLType          	nvarchar(100) = NULL,
@Executive nvarchar(250) = Null,
@Leader int = Null,
@FA nvarchar (250) = Null,
@Status nvarchar (250) = Null
-----------------------------------

If @SQLType = 'Full_Filter'
begin
Declare @FilterByExecutive nvarchar(250)
Declare @FilterByLeader nvarchar(250)

      if  @Executive > 0
            begin
                  select @FilterByExecutive = 1
            end 
      else
            begin
                  select @FilterByExecutive = 0
            end 

      if  @Leader > 0
            begin
                  select @FilterByLeader = 1
            end 
      else
            begin
                 select @FilterByLeader = 0
            end 

					SELECT PMO_Projects.project_number, PMO_Projects.project_name, 
					PMO_Executives.executive_full, 
					PMO_Employees.[Last Name] + N',  ' + PMO_Employees.[First Name] 
					AS project_lead, PMO_Functional_Area.functional_area, PMO_Projects.completion, PMO_Projects.project_start_date, PMO_Projects.project_end_date, PMO_Project_Status.code FROM 
					PMO_Projects LEFT OUTER JOIN PMO_Functional_Area ON 
					PMO_Projects.functional_area = PMO_Functional_Area.id
					 LEFT OUTER JOIN PMO_Employees ON 
					PMO_Projects.project_lead = PMO_Employees.ID 
					LEFT OUTER JOIN PMO_Executives ON 
					PMO_Projects.executive_sponsor = PMO_Executives.ID
					LEFT OUTER JOIN PMO_Project_Status   on
					PMO_PROJECT_Status.ID = PMO_Projects.Project_Status
                  WHERE             
                        (@FilterByExecutive=1 AND Executive_full = @Executive )				          
                        OR 
                        (@FilterByLeader=1 AND Project_lead  =  @Leader)
					ORDER BY PMO_Projects.project_number

      End


-------THIS IS THE EXAMPLE OF THE FULL QUERY WITHOUT ANY SELECTION----
If @SQLType = 'Full_Filterx'
Begin
SELECT PMO_Projects.project_number, PMO_Projects.project_name, 
PMO_Executives.executive_full, 
PMO_Employees.[Last Name] + N',  ' + PMO_Employees.[First Name] 
AS project_lead, PMO_Functional_Area.functional_area, PMO_Projects.completion, PMO_Projects.project_start_date, PMO_Projects.project_end_date, PMO_Project_Status.code FROM 
PMO_Projects LEFT OUTER JOIN PMO_Functional_Area ON 
PMO_Projects.functional_area = PMO_Functional_Area.id
 LEFT OUTER JOIN PMO_Employees ON 
PMO_Projects.project_lead = PMO_Employees.ID 
LEFT OUTER JOIN PMO_Executives ON 
PMO_Projects.executive_sponsor = PMO_Executives.ID
LEFT OUTER JOIN PMO_Project_Status   on
PMO_PROJECT_Status.ID = PMO_Projects.Project_Status
ORDER BY PMO_Projects.project_number
End

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-30 : 12:30:37

You can do it like this because your searchparms are coming up with NULL if not set:
WHERE @Executive IS NULL or Executive_full = @Executive
...

No, you're never too old to Yak'n'Roll if you're too young to die.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2009-12-30 : 12:38:20

Thank you webfred, I appreciated, is half of the solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 01:42:40

i think your where clause should be


WHERE             (
                        (@Executive IS NULL OR Executive_full = @Executive )				          
                        AND
                        (@Leader IS NULL OR Project_lead  =  @Leader)
                  )
OR @SQLType = 'Full_Filterx'

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2010-01-04 : 17:10:19

------HERE IS THE ANSWER ON HOW TO SELECT MULTIPLE FILTERS AT THE SAME TIME ----
------I AM POSTING THE ANSWER TO HELP FELLOW DEVELOPERS AND IN GRATITUDE TO THIS SITE -------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Pick stored procedure Name]
(
@SQLType nvarchar(100) = NULL,
@Executive nvarchar(250) = Null,
@Leader int = Null,
@FA nvarchar (250) = Null,
@Status nvarchar (250) = Null
)
AS
/********************************************************************
* Program Name : PMO_sp_Filter
*
* Create Date : 11/02/2009
*
* Author : Osirisa
*
* Purpose : Create a Gridview Filter for the Main Page
*********************************************************************
*
* Modification History:
*
*********************************************************************

--*************************************************************
--SET SQL STMT BASED ON SQL TYPE PASSED IN
--*************************************************************/
Declare @Leader2 int
Set @Leader2 = @Leader

If @SQLType = 'Filters'
begin

-- Handle Empty Paramaters.
If
@Executive IS NULL
SET @Executive = '%'

If
@Leader IS NULL
SET @Leader = '0'
If @Leader = '0' SET @Leader2 ='1000'

IF
@FA IS NULL
SET @FA = '%'

IF
@Status IS NULL
SET @Status = '%'

-- Insert statements for procedure here

SELECT PMO_Projects.project_number, PMO_Projects.project_name,
PMO_Executives.executive_full,
PMO_Employees.[Last Name] + N', ' + PMO_Employees.[First Name]
AS project_lead, PMO_Functional_Area.functional_area, PMO_Projects.completion, PMO_Projects.project_start_date, PMO_Projects.project_end_date, PMO_Project_Status.code FROM
PMO_Projects LEFT OUTER JOIN PMO_Functional_Area ON
PMO_Projects.functional_area = PMO_Functional_Area.id
LEFT OUTER JOIN PMO_Employees ON
PMO_Projects.project_lead = PMO_Employees.ID
LEFT OUTER JOIN PMO_Executives ON
PMO_Projects.executive_sponsor = PMO_Executives.ID
LEFT OUTER JOIN PMO_Project_Status on
PMO_PROJECT_Status.ID = PMO_Projects.Project_Status
WHERE
((Executive_full LIKE @Executive) and (Project_Lead BETWEEN @Leader and @Leader2 ) and (PMO_Functional_Area.functional_area Like @FA) and (PMO_Project_Status.code Like @Status))
ORDER BY PMO_Projects.project_number
END

If @SQLType = 'Full_Filter'
Begin
SELECT PMO_Projects.project_number, PMO_Projects.project_name,
PMO_Executives.executive_full,
PMO_Employees.[Last Name] + N', ' + PMO_Employees.[First Name]
AS project_lead, PMO_Functional_Area.functional_area, PMO_Projects.completion, PMO_Projects.project_start_date, PMO_Projects.project_end_date, PMO_Project_Status.code FROM
PMO_Projects LEFT OUTER JOIN PMO_Functional_Area ON
PMO_Projects.functional_area = PMO_Functional_Area.id
LEFT OUTER JOIN PMO_Employees ON
PMO_Projects.project_lead = PMO_Employees.ID
LEFT OUTER JOIN PMO_Executives ON
PMO_Projects.executive_sponsor = PMO_Executives.ID
LEFT OUTER JOIN PMO_Project_Status on
PMO_PROJECT_Status.ID = PMO_Projects.Project_Status
ORDER BY PMO_Projects.project_number
End

SELECT @LEADER
SELECT @LEADER2

balaganapathy.n
Starting Member

18 Posts

Posted - 2010-01-05 : 02:12:45

I guess the procedure can be reduced like below

ALTER PROCEDURE [dbo].[Pick stored procedure Name]
(
@SQLType nvarchar(100) = NULL,
@Executive nvarchar(250) = Null,
@Leader int = Null,
@FA nvarchar (250) = Null,
@Status nvarchar (250) = Null
)
AS
BEGIN

SELECT PMO_Projects.project_number, PMO_Projects.project_name,
PMO_Executives.executive_full,
PMO_Employees.[Last Name] + N', ' + PMO_Employees.[First Name]
AS project_lead, PMO_Functional_Area.functional_area, PMO_Projects.completion, PMO_Projects.project_start_date, PMO_Projects.project_end_date, PMO_Project_Status.code FROM
PMO_Projects LEFT OUTER JOIN PMO_Functional_Area ON
PMO_Projects.functional_area = PMO_Functional_Area.id
LEFT OUTER JOIN PMO_Employees ON
PMO_Projects.project_lead = PMO_Employees.ID
LEFT OUTER JOIN PMO_Executives ON
PMO_Projects.executive_sponsor = PMO_Executives.ID
LEFT OUTER JOIN PMO_Project_Status on
PMO_PROJECT_Status.ID = PMO_Projects.Project_Status
WHERE CASE WHEN @Executive IS NOT NULL THEN Executive_full LIKE @Executive ELSE 1 END
AND CASE WHEN @Leader IS NOT NULL THEN Project_Lead = @Leader ELSE 1 END
AND CASE WHEN @FA IS NOT NULL THEN PMO_Functional_Area.functional_area LIKE @FA ELSE 1 END
AND CASE WHEN @Status IS NOT NULL THEN PMO_Project_Status.code LIKE @Status ELSE 1 END
ORDER BY PMO_Projects.project_number

END

And, i think you dont need the parameter [@SQLType]

balaganapathy n.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-05 : 03:00:31

quote:
Originally posted by osirisa

------HERE IS THE ANSWER ON HOW TO SELECT MULTIPLE FILTERS AT THE SAME TIME ----
------I AM POSTING THE ANSWER TO HELP FELLOW DEVELOPERS AND IN GRATITUDE TO THIS SITE -------

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Pick stored procedure Name]
(
@SQLType nvarchar(100) = NULL,
@Executive nvarchar(250) = Null,
@Leader int = Null,
@FA nvarchar (250) = Null,
@Status nvarchar (250) = Null
)
AS
/********************************************************************
* Program Name : PMO_sp_Filter
*
* Create Date : 11/02/2009
*
* Author : Osirisa
*
* Purpose : Create a Gridview Filter for the Main Page
*********************************************************************
*
* Modification History:
*
*********************************************************************

--*************************************************************
--SET SQL STMT BASED ON SQL TYPE PASSED IN
--*************************************************************/
Declare @Leader2 int
Set @Leader2 = @Leader

If @SQLType = 'Filters'
begin

-- Handle Empty Paramaters.
If
@Executive IS NULL
SET @Executive = '%'

If
@Leader IS NULL
SET @Leader = '0'
If @Leader = '0' SET @Leader2 ='1000'

IF
@FA IS NULL
SET @FA = '%'

IF
@Status IS NULL
SET @Status = '%'

-- Insert statements for procedure here

SELECT PMO_Projects.project_number, PMO_Projects.project_name,
PMO_Executives.executive_full,
PMO_Employees.[Last Name] + N', ' + PMO_Employees.[First Name]
AS project_lead, PMO_Functional_Area.functional_area, PMO_Projects.completion, PMO_Projects.project_start_date, PMO_Projects.project_end_date, PMO_Project_Status.code FROM
PMO_Projects LEFT OUTER JOIN PMO_Functional_Area ON
PMO_Projects.functional_area = PMO_Functional_Area.id
LEFT OUTER JOIN PMO_Employees ON
PMO_Projects.project_lead = PMO_Employees.ID
LEFT OUTER JOIN PMO_Executives ON
PMO_Projects.executive_sponsor = PMO_Executives.ID
LEFT OUTER JOIN PMO_Project_Status on
PMO_PROJECT_Status.ID = PMO_Projects.Project_Status
WHERE
((Executive_full LIKE @Executive) and (Project_Lead BETWEEN @Leader and @Leader2 ) and (PMO_Functional_Area.functional_area Like @FA) and (PMO_Project_Status.code Like @Status))
ORDER BY PMO_Projects.project_number
END

If @SQLType = 'Full_Filter'
Begin
SELECT PMO_Projects.project_number, PMO_Projects.project_name,
PMO_Executives.executive_full,
PMO_Employees.[Last Name] + N', ' + PMO_Employees.[First Name]
AS project_lead, PMO_Functional_Area.functional_area, PMO_Projects.completion, PMO_Projects.project_start_date, PMO_Projects.project_end_date, PMO_Project_Status.code FROM
PMO_Projects LEFT OUTER JOIN PMO_Functional_Area ON
PMO_Projects.functional_area = PMO_Functional_Area.id
LEFT OUTER JOIN PMO_Employees ON
PMO_Projects.project_lead = PMO_Employees.ID
LEFT OUTER JOIN PMO_Executives ON
PMO_Projects.executive_sponsor = PMO_Executives.ID
LEFT OUTER JOIN PMO_Project_Status on
PMO_PROJECT_Status.ID = PMO_Projects.Project_Status
ORDER BY PMO_Projects.project_number
End

SELECT @LEADER
SELECT @LEADER2

you dont need if conditions. you can just reduce it using where condition i showed you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources