CREATE Procedure up_get_job_invoice_details(
@StartDate AS VarChar(10)='',
@EndDate AS VarChar(10)='',
@ClientID as VARCHAR(8)='All',
@CDSContact as VARCHAR(8)='All',
@InvStatus as VARCHAR(20)='All')

AS

/* Testing Variable

DECLARE @StartDate AS VarChar(10)
DECLARE @EndDate AS VarChar(10)
DECLARE @InvStatus as VARCHAR(20)
DECLARE @ClientID as VARCHAR(8)


SET @StartDate='01/01/2004'
SET @EndDate='01/09/2004'
SET @InvStatus ='All'
--SET @InvStatus ='WS4'
--SET @ClientID ='All'
SET @ClientID ='197CA'

*/

SET NOCOUNT ON
SET ARITHABORT ON
SET XACT_ABORT ON

DECLARE @SDate AS DATETIME
DECLARE @EDate AS DATETIME

-- If Start Date is blank then set start date as '01/01/1998'
IF @StartDate=''
	SET @SDate= CONVERT(DATETIME,'01/01/1998', 103)
ELSE
	SET @SDate= CONVERT(DATETIME, @StartDate, 103)

-- If End Date is blank then set end date as tomorrow's date
IF @EndDate=''
	SET @EDate=GETDATE()+1
ELSE
	SET @EDate=CONVERT(DATETIME, @EndDate, 103)+1

--print @SDate
--print @EDate




CREATE TABLE #Job (JobNumber VarChar(10),JobName VarChar(50),ClientID Varchar(8))
CREATE TABLE #InvoiceStatus(InvoiceStatus Varchar(4))
CREATE TABLE #Client(ClientID Varchar(8))


IF 	@CDSContact = 'All'
		INSERT INTO #Job
		select JobNumber,JobName,ClientID
		from dbo.Job
		where OrderDate >= @SDate AND OrderDate < @EDate
ELSE
		INSERT INTO #Job
		select J.JobNumber,J.JobName,J.ClientID
		from dbo.Job J
		INNER JOIN CiRelationShip CI ON J.JobNumber=CI.ContractID
		where J.OrderDate >= @SDate AND J.OrderDate < @EDate AND
		CI.ResourceID=Cast(@CDSContact as INT)


IF 	@InvStatus = 'All'
		INSERT INTO #InvoiceStatus(InvoiceStatus)
		select distinct workstatus
		from ScheduleItems 
		WHERE PROCESSID='IV'
ELSE
		INSERT INTO #InvoiceStatus(InvoiceStatus)
		select @InvStatus


IF 	@ClientID = 'All'
		INSERT INTO #Client(ClientID)
		select DISTINCT ClientID from Job
ELSE
		INSERT INTO #Client(ClientID)
		select @ClientID

SELECT SI.JobNumber,
J.JobName,
J.ClientID,
Comp.CompanyName,
JI.InvoiceNo,
C.CodeText as "InvStatus",
SI.ItemID
FROM dbo.ScheduleItems SI
INNER JOIN dbo.Codes C ON SI.WorkStatus=C.CodeID
INNER JOIN #Job J ON SI.JobNumber=J.JobNumber
INNER JOIN dbo.Company Comp ON J.ClientID=Comp.CiID
INNER JOIN dbo.JobInvoice JI ON SI.JobNumber=JI.JobNumber
INNER JOIN #InvoiceStatus INV_ST ON SI.WorkStatus=INV_ST.InvoiceStatus
INNER JOIN #Client CL ON J.ClientID=CL.ClientID
WHERE SI.ProcessID='IV' AND SI.ProductNumber=1 AND
--JI.InvoiceNo IS NOT NULL AND 
LEN(JI.InvoiceNo)>0
ORDER BY SI.JobNumber


DROP TABLE #Job
DROP TABLE #InvoiceStatus
DROP TABLE #Client
GO

quote:

---

Originally posted by tduggan

Why are you using Vyas' solution for arrays if you aren't using arrays?

Tara

---

quote:

---

Originally posted by jsmith8858


Personally, I would accept NULL values where you are not filtering on a particular column (instead of 'All'), and then you can write:

where ClientID like isnull(@ClientID, '%')

- Jeff

---

quote:

---

Posted - 10/12/2004 : 15:51:23
--------------------------------------------------------------------------------

My worry is when I have something like

WHERE COL LIKE ISNULL(@SomeVar,'%')
OR COL2 LIKE ISNULL(@SomeVar2,'%')

---