CREATE PROCEDURE [dbo].[pPMPERDET]
@START_DATE----DATETIME,
@START_TIME----DATETIME,
@END_DATE------DATETIME,
@END_TIME------DATETIME,
@FAC_REGION----char (2),
@FAC_SECTOR----char (2),
@FAC_SUBSECTOR-char,
@AREA----------varchar (6),
@FAC_MPS-------varchar (4),
@WR_CREW_UNIT--char (4),
@WR_WATCH------char (1)
AS
BEGIN
SELECT
fields from table1, table2 and table3
FROM ((mmssql.dbo.table1 table1 INNER JOIN
mmssql.dbo.table2 table2 ON table1.field1 = table2.field1)
LEFT OUTER JOIN mmssql.dbo.table3 table3 ON
table2.field1 = table3.field1)
WHERE
[b]-- fixed selection criteria[/b]
(table2.DIRECTIVE_LEVEL = 'N')
AND
(table2.ENTRY_TYPE = 'LPM')
AND
(table1.VOID_STATUS <> 'V' OR table1.VOID_STATUS <> '') AND
[b]-- Parameter selection criteria[/b]
(table2.LATEST_DATE >= @START_DATE AND table2.LATEST_DATE <= @END_DATE) AND
(table2.WR_AREA = @AREA OR @AREA = '') AND
(table2.WR_CREW_UNIT = @WR_CREW_UNIT OR @WR_CREW_UNIT = '') AND
(table2.WR_WATCH = @WR_WATCH OR @WR_WATCH = '') AND
-- FAC_REGION, SECTOR, SUBSECTOR & SMO
(
( table2.FAC_REGION= @FAC_REGION AND
(
( LEN(@FAC_SECTOR)=1 AND table2.FAC_SECTOR LIKE @FAC_SECTOR + '*' ) --search by SMO
OR
( table2.FAC_SECTOR=@FAC_SECTOR AND
( table2.FAC_SUBSECTOR= @FAC_SUBSECTOR OR @FAC_SUBSECTOR= '' )
)
OR @FAC_SECTOR = ''
)
)
OR @FAC_REGION = ''
)
END
GO
