Multi Param Proc & Coalesce problem

[JSpicolli]

Hi,

Hopefully some of you SQL Guru's can help me out with my dilemma.

I am developing a report that takes multiple input params via a webform.

The users want to be able to search on optional multi-field criteria. They can select as many field values as they want and the search will return the results matching their search pattern.

I thought coalesce would work, and it does with one exception. I only want to return a single record per incident (Incident_PK).

Coalesce wont work in this situation since if the incident number is omitted, the proc will join back to that incident number and return multiple records per incident.

Something else is also flawed with my logic since even if I enter an incident Number (incident_pk), it still return's multiple records per incident.

Can someone please take a look at this code and see if anything strikes you as being flawed in my logic?

Many Many Thanks.

usp_ManageCallsSearch '','',0,'','',0,0,'OPEN'



EXEC usp_ManageCallsSearch 'ALL','ALL',0,'','',0,2681,'OPEN', 0

DROP PROCEDURE dbo.usp_ManageCallsSearch	

CREATE PROCEDURE dbo.usp_ManageCallsSearch	
	@DateBegin varchar(50) = '01/01/1980',
	@DateEnd varchar(50) = '12/01/2020', 
	@UrgencyLevel int = 0,
	@RDL varchar (50) = '',
	@DTL varchar (50) = '',
	@Product int,
	@IncidentNumber int = 0,
	@CallStatus varchar(30) = 'OPEN',
	@SiteName int = Null

 AS

DECLARE @SiteID int
DECLARE @UrgencyID int
DECLARE @IncidentID int
DECLARE @StatusID int
DECLARE @DateSearchBegin SmallDateTime
DECLARE @DateSearchEnd SmallDateTime
DECLARE @ProductID int


SELECT @IncidentID = Case
	WHEN @IncidentNumber = 0 Then Null
	WHEN @IncidentNumber = '' Then Null
	ELSE @IncidentNumber 
END


SELECT @StatusID = Case
	WHEN @CallStatus = 'OPEN' THEN 1
	WHEN @CallStatus = 'CLOSED' THEN 2
	WHEN @CallStatus = 'ASSIGNED' THEN 3
	ELSE Null 
END

SELECT @UrgencyID = Case
	WHEN @UrgencyLevel = 0 Then Null
	WHEN @UrgencyLevel = '' Then Null
	ELSE @UrgencyLevel
End

SELECT @ProductID = Case
	WHEN @Product = 0 Then Null
	WHEN @Product = '' Then Null
	ELSE @Product
END


SELECT @DateSearchBegin = CASE
	WHEN @DateBegin = '' THEN Cast('01/01/1980' as SmallDateTime)
	WHEN @DateBegin = 'ALL' THEN Cast('01/01/1980' as SmallDateTime)
	WHEN @DateBegin = Null THEN Cast('01/01/1980' as SmallDateTime)
	ELSE
		Cast(@DateBegin as SmallDateTime)
END


SELECT @DateSearchEnd = CASE
	WHEN @DateEnd = '' THEN Cast('12/01/2020' as SmallDateTime)
	WHEN @DateEnd = 'ALL' THEN Cast('12/01/2020' as SmallDateTime)
	WHEN @DateEnd = Null THEN Cast('12/01/2020' as SmallDateTime)
	ELSE
		Cast(@DateEnd as SmallDateTime)
END

SELECT @SiteID = CASE
	WHEN @SiteName > 0 Then @SiteName
	ELSE
		Null
END
	

SELECT
	DISTINCT tblIncident.incident_PK,
	customerdirectory.dbo.tblCustomers.customerName, 
	tblProducts.product_name, 
	tblUrgency.urgency_name, 
	tblStatus.status_name, 
	tblStatus.status_PK,  
	tblIncident.problem_desc, 
	--tblIncident.incident_PK AS Expr1, 
	tblEvents.event_datetime, 
	tblEventType.eventtype_PK, 
		(
			select 
				top 1 
					tblevents.event_datetime 
			from 
				tblevents 
			where 
				tblevents.eventtype_fK = 9 and tblEvents.incident_FK = tblIncident.incident_PK 
			order by tblevents.event_datetime desc
		)
		as DateClosed 
FROM 

	tblIncident 

INNER JOIN 	customerdirectory.dbo.tblCustomers 
		ON 
	tblIncident.customer_FK = customerdirectory.dbo.tblCustomers.customer_pk 
		AND
	customerdirectory.dbo.tblCustomers.customer_pk = COALESCE(@SiteID, customerdirectory.dbo.tblCustomers.customer_pk)

LEFT OUTER JOIN tblProducts 
		ON 
	tblIncident.product_FK = tblProducts.product_PK 

INNER JOIN 	tblStatus 
		ON 
	tblIncident.status_FK = tblStatus.status_PK 
		AND
	tblProducts.product_PK = COALESCE(@ProductID, tblProducts.product_PK)

INNER JOIN tblUrgency 
		ON 
	tblIncident.urgency_FK = tblUrgency.urgency_PK
			AND
	tblUrgency.urgency_PK = COALESCE(@UrgencyID, tblUrgency.urgency_PK) 

INNER JOIN tblEvents 
		ON 
	tblIncident.incident_PK = tblEvents.incident_FK 
			AND
		tblEvents.event_Datetime BETWEEN @DateSearchBegin AND @DateSearchEnd

INNER JOIN tblEventType 
		ON 
	tblEvents.eventtype_FK = tblEventType.eventtype_PK 
			AND 
	tblEvents.eventtype_FK = tblEventType.eventtype_PK 

--WHERE tblIncident.incident_PK = COALESCE(@IncidentID, tblIncident.incident_PK) 
			--AND
		WHERE tblIncident.status_FK = COALESCE( @StatusID, tblIncident.status_FK )

ORDER BY incident_pk desc



GO

 

[JSpicolli]

I fixed the problem myself.

In case anyone is interested the problem was the join on tblEvents. An incident can have multiple event records, so that was the issue:

The resolution I used was to create a new variable:

SELECT @EventID = CASE
	WHEN @StatusID = 1 Then 8
	WHEN @StatusID = 2 THEN 9
	WHEN @StatusID = 3 THEN 14
	ELSE 8
END

And then edit the join to:

INNER JOIN tblEvents 
		ON 
	tblIncident.incident_PK = tblEvents.incident_FK 
			AND
		[highlight]tblEvents.eventtype_fk = @EventID[/highlight]
	       AND
		tblEvents.event_Datetime BETWEEN @DateSearchBegin AND @DateSearchEnd

WTH, I can't even give myself a star hehe