Two optional parameters in where clause

Two optional parameters in where clause

Post by Mik » Fri, 27 Jun 2003 01:55:56



Hi

I have a Stored Procedure script that started out quite simple but is
turning into a monster . Unfortunately My SQl knowledge is a little
limited although I try hard. So Far I have a select statement
selecting from five tables that consists of a main table with  three
leftouter joins and an inner join to the other four tables.
Unfortunately the main table is hierarchical so a cursor is required
to step through a field in the main table using The first optional
parameter. And I'm sorry this gets more complicated believe me I am
but I need another optional parameter to split the results into
business units.Ie a user can enter parameter 1 or parameter two or
both to return the required results. If the parameters are'nt included
I want the query to return all values for that parameter. I have tried
a few things like 'or' and 'like '%'' and 'isnull' in the where clause
without much success. I've included the script if someone could please
give me some pointers I'd be really appreciative.

Thankyou
Michael Black

IF EXISTS (SELECT name
           FROM   sysobjects
           WHERE  name = N'CHHT_GetExpend'
           AND    type = 'P')
    DROP PROCEDURE CHHT_GetExpend
GO

CREATE PROCEDURE CHHT_GetExpend


AS

SELECT  Wo.Wonum, WO.Description,
WO.parent,WO.location,WO.estatapprlabhrs,WO.estatapprlabcost,WO.estatapprmatcost,WO.estatapprtoolcost,WO.estatapprservcost,SUBSTRING(WO.location,1,4)
bu,
L.description, LT.regularhrs, LT.othrs, LT.linecost, MT.linecost,
TT.linecost

From  WORKORDER WO LEFT OUTER JOIN labtrans LT on WO.wonum = LT.refwo
                   LEFT OUTER JOIN tooltrans TT on WO.wonum = TT.refwo
                   LEFT OUTER JOIN matusetrans MT on WO.wonum = MT.refwo,
                   Locations L                    


substring/
Where

and  SUBSTRING(WO.location,1,4) = L.location
DECLARE SubWONums CURSOR LOCAL FOR

OPEN SubWONums




        END
CLOSE SubWONums
DEALLOCATE SubWONums

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

IF EXISTS (SELECT name
           FROM   sysobjects
           WHERE  name = N'View_ExpSummary'
           AND    type = 'P')
    DROP PROCEDURE View_ExpSummary
GO


AS

CREATE TABLE #ExpSum ( WONUM VARCHAR (100), DESCRIPTION VARCHAR (100),
PARENT VARCHAR (10),
LOCATION VARCHAR (25), ESTATAPPRLABHRS FLOAT, ESTATAPPRLABCOST DECIMAL
(10,2), ESTATAPPRMATCOST DECIMAL (10,2), ESTATAPPRTOOLCOST DECIMAL
(10,2),
ESTATAPPRSERVCOST DECIMAL (10,2), SUBSTRI VARCHAR (6), LocDesc VARCHAR
(65), REGULARHRS FLOAT, OTHRS FLOAT, LABLINECOST DECIMAL (15,2),
MATLINECOST DECIMAL (15,2), TOOLLINECOST DECIMAL (15,2) )
INSERT INTO #ExpSum

Select * from #ExpSum

GO

exec View_Expsummary '1045', '55CP'

 
 
 

Two optional parameters in where clause

Post by navs » Fri, 27 Jun 2003 11:44:53


Hi,
change the signatures of the sp as

hope this will fix your problem.

CREATE PROCEDURE CHHT_GetExpend


AS


/*
ur sp logic goes in here
*/

>-----Original Message-----
>Hi

>I have a Stored Procedure script that started out quite
simple but is
>turning into a monster . Unfortunately My SQl knowledge
is a little
>limited although I try hard. So Far I have a select
statement
>selecting from five tables that consists of a main table
with  three
>leftouter joins and an inner join to the other four
tables.
>Unfortunately the main table is hierarchical so a cursor
is required
>to step through a field in the main table using The first
optional
>parameter. And I'm sorry this gets more complicated
believe me I am
>but I need another optional parameter to split the
results into
>business units.Ie a user can enter parameter 1 or
parameter two or
>both to return the required results. If the parameters
are'nt included
>I want the query to return all values for that parameter.
I have tried
>a few things like 'or' and 'like '%'' and 'isnull' in the
where clause
>without much success. I've included the script if someone
could please
>give me some pointers I'd be really appreciative.

>Thankyou
>Michael Black

>IF EXISTS (SELECT name
>       FROM   sysobjects
>       WHERE  name = N'CHHT_GetExpend'
>       AND    type = 'P')
>    DROP PROCEDURE CHHT_GetExpend
>GO

>CREATE PROCEDURE CHHT_GetExpend


>AS

>SELECT  Wo.Wonum, WO.Description,
>WO.parent,WO.location,WO.estatapprlabhrs,WO.estatapprlabco

st,WO.estatapprmatcost,WO.estatapprtoolcost,WO.estatapprser
vcost,SUBSTRING(WO.location,1,4)
Quote:>bu,
>L.description, LT.regularhrs, LT.othrs, LT.linecost,
MT.linecost,
>TT.linecost

>From  WORKORDER WO LEFT OUTER JOIN labtrans LT on

WO.wonum = LT.refwo
Quote:>               LEFT OUTER JOIN tooltrans TT on
WO.wonum = TT.refwo
>               LEFT OUTER JOIN matusetrans MT on

WO.wonum = MT.refwo,
>               Locations L                    


parameter it is
>substring/
>Where



- Show quoted text -

>and  SUBSTRING(WO.location,1,4) = L.location
>DECLARE SubWONums CURSOR LOCAL FOR

-and istask = 'N'
>OPEN SubWONums




>    END
>CLOSE SubWONums
>DEALLOCATE SubWONums

>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO

>IF EXISTS (SELECT name
>       FROM   sysobjects
>       WHERE  name = N'View_ExpSummary'
>       AND    type = 'P')
>    DROP PROCEDURE View_ExpSummary
>GO


>AS

>CREATE TABLE #ExpSum ( WONUM VARCHAR (100), DESCRIPTION
VARCHAR (100),
>PARENT VARCHAR (10),
>LOCATION VARCHAR (25), ESTATAPPRLABHRS FLOAT,

ESTATAPPRLABCOST DECIMAL
Quote:>(10,2), ESTATAPPRMATCOST DECIMAL (10,2),

ESTATAPPRTOOLCOST DECIMAL

- Show quoted text -

>(10,2),
>ESTATAPPRSERVCOST DECIMAL (10,2), SUBSTRI VARCHAR (6),
LocDesc VARCHAR
>(65), REGULARHRS FLOAT, OTHRS FLOAT, LABLINECOST DECIMAL
(15,2),
>MATLINECOST DECIMAL (15,2), TOOLLINECOST DECIMAL (15,2) )
>INSERT INTO #ExpSum

>Select * from #ExpSum

>GO

>exec View_Expsummary '1045', '55CP'
>.


 
 
 

1. Evaluation Order of Optional Where Clause Parameters

Hi,

I have a stored procedure that looks basically as follows:

select *
  from my_table




My tests indicate that the right side of the OR is being evaluated first (at
least sometimes), which is ineffecient when my parameter variables are null.
I tried nested CASE statements, and performance decreased significantly.

Is dynamic sql the way to go here?  I'd like for all of the parameters to be
optional.

Thanks,
Joe

2. Dynamically changing DDQ's Text source file name

3. Optional Parameters to SP - no value, one, or several values per parameter

4. Any Info about Universe and Delphi5?

5. Using Parameter Queries with Optional Parameters

6. Database structure for question/answer data storage

7. passing optional parameters to the parameters collection

8. Arrays()

9. WHERE Clause using Optional Paramters

10. Allowing for Optional Full Text Search Clauses in WHERE Statement

11. Trying to use optional contains in where clause

12. Specifying optional WHERE clause

13. Optional where clause in Stored Procedures