Optional Parameters

[Chopstik]

A wee bit of searching has helped me to figure out how to use optional parameters in a stored procedure. However, I'm having some issues because of the manner in which I had to build the procedure and the joins. In short, I need to build a new reporting section of trouble tickets for an existing application/database that has been running for the last 6 months. All of the tables in this particular procedure are populated with data except the T_STAT table which is a new table that has no data (yet). I want to build a search functionality front-end where the user can search for tickets based on several options (all of which are included in the SP as optional parameters). My problem is that I have a left join to the empty T_STAT table which is ok until I add in the optional parameter in my WHERE statement - which automatically converts my LEFT JOIN into an INNER JOIN - and will return no results! I've tried a couple of variations to see if I could find a way around it but nothing has worked so far. Any other suggestions?

CREATE PROCEDURE dbo.SP_SEARCH_TICKETS
	@TicketID	bigint = NULL,
	@PropName	varchar(50) = NULL,
	@BeginDt		datetime = NULL,
	@EndDt		datetime = NULL,
	@District	varchar(15) = NULL,
	@ProblemType	int = NULL,
	@Status		varchar(50) = NULL,
	@BOM		int = NULL
AS
SELECT T.tckt_id, P.prpty_nm, T.eff_dt, P.dist_cd, T.tckt_ty_cat_cd, S.stat_ty_cd, B.bom_grp_id
FROM T_TCKT T INNER JOIN T_TCKT_PRPTY TP ON T.tckt_id = TP.tckt_id
INNER JOIN T_PRPTY P ON TP.prpty_id = P.prpty_id
INNER JOIN (SELECT prpty_id, bom_grp_id
	FROM T_BOM_GRP_CAT 
	WHERE tckt_ty_cd = 1) B ON P.prpty_id = B.prpty_id
LEFT JOIN (SELECT tckt_id, stat_ty_cd FROM T_STAT WHERE stat_chng_dt = (SELECT MAX(stat_chng_dt) FROM T_STAT GROUP BY tckt_id)) S
	ON T.tckt_id = S.tckt_id

WHERE
	T.tckt_id LIKE CASE
		WHEN @TicketID IS NULL THEN '%'
		ELSE @TicketID END
	AND 
	P.prpty_nm LIKE CASE
		WHEN @PropName IS NULL THEN '%'
		ELSE @PropName END
	AND
	T.eff_dt >= CASE
		WHEN @BeginDt IS NULL THEN GETDATE()
		ELSE @BeginDt END
	AND
	T.eff_dt >= CASE
		WHEN @EndDt IS NULL THEN GETDATE()
		ELSE @EndDt END
	AND
	P.dist_cd LIKE CASE
		WHEN @District IS NULL THEN '%'
		ELSE @District END
	AND
	T.tckt_ty_cat_cd LIKE CASE
		WHEN @ProblemType IS NULL THEN '%'
		ELSE @ProblemType END
	AND
	S.stat_ty_cd LIKE CASE
		WHEN @Status IS NULL THEN '%'
		ELSE @Status END
	AND
	B.bom_grp_id LIKE CASE
		WHEN @BOM IS NULL THEN '%'
		ELSE @BOM END

ORDER BY T.tckt_id

GO

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[gmmastros]

try moving the condition to the join clause.

LEFT JOIN (SELECT tckt_id, stat_ty_cd FROM T_STAT WHERE stat_chng_dt = (SELECT MAX(stat_chng_dt) FROM T_STAT GROUP BY tckt_id)) S
    ON T.tckt_id = S.tckt_id
[!]	AND T.tckt_id LIKE CASE
        WHEN @TicketID IS NULL THEN '%'
        ELSE @TicketID END[/!]
WHERE
    P.prpty_nm LIKE CASE
        WHEN @PropName IS NULL THEN '%'
        ELSE @PropName END
    AND

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chopstik]

That works beautifully! I'd been knocking my head on this all afternoon, so at least now I can relax a little bit this weekend... Thanks!

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[gmmastros]

Hey ya chops. I hope you don't read this until Monday morning (so that you can enjoy your weekend).

Your method for allowing optional parameters can cause you to miss data if your column(s) allow NULL values. Run this code to see what I mean.

Declare @Temp Table(Data VarChar(20))

Insert Into @Temp Values('gmmastros')
Insert Into @Temp Values('Chopstik')
Insert Into @Temp Values('')
Insert Into @Temp Values(NULL)

Declare @Name VarChar(20)
Set @Name = NULL

Select *
From   @Temp
Where  Data Like Case When @Name Is NULL Then '%' Else @Name End

When you run this, you will not get the row from the table where data is null. You only get 3 rows instead of 4.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

And is the server smart enough to convert LIKE '%' to NOT NULL? I'd want to compare for execution plans and performance:

WHERE @Name IS NULL OR Data LIKE @Name
--and
WHERE Data LIKE CASE WHEN @Name IS NULL THEN '%' ELSE @Name END

 

[Chopstik]

George,

Thanks for that heads up. Not something I'd considered, and I don't know that it's problem yet, but I'll take a look at it on Monday morning. And don't worry, it hasn't ruined my weekend. It's actually been a decent weekend so far, so I'll keep my fingers crossed that it remains so until I look at this again on Monday morning. ;-)

ESquared,

I considered both and went with the second as I didn't consider there to be a performance hit (but I didn't test for it, either). But, I'm certainly not against testing it now to see if one comes out better than the other. Thanks.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike