How do I have an INT variable default to the wildcard if NULL in a Stored Procedure..?

Question

How do I have an INT variable default to the wildcard if NULL in a Stored Procedure..?

RunnerIE

Say Hey Kid

Points: 665
More actions
August 5, 2009 at 5:01 am

#134565

Hi - I'm trying to use the one Stored Procedure for different scenarios... I want to pass any combination of three INTeger variables and sort my search using the WHERE command accordingly. Works well when all three varaibles are specified, but I'm trying to figure out how to default to a wildcard if the variable is not passed by the EXEC command... All help very appreciated.
Here is my Stored Procedure:
ALTER PROCEDURE dbo.sp_Status_3
(
@myLocationRef int,
@myReviewerRef int =NULL,
@myBURef int =NULL
)
AS
SELECT *
FROM dbo.tmp_sp_Status_2
WHERE (ReviewerRef = @myReviewerRef) AND (BURef = @myBURef) AND (LocationRef = @myLocationRef)
Thanks,
Brian.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

matt6288 SSCertifiable Points: 5058 More actions · Answer 1

I can't test this yet but I should be able to soon. It appears that @myLocationRef is required to be passed. If it isn't you can use the same logic as the other 2 columns.

ALTER PROCEDURE dbo.sp_Status_3

(

@myLocationRef int,

@myReviewerRef int =NULL,

@myBURef int =NULL

)

AS

SELECT *

FROM dbo.tmp_sp_Status_2

WHERE (ReviewerRef = isnull(@myReviewerRef,ReviewRef))

AND (BURef = isnull(@myBURef,BURef))

AND (LocationRef = @myLocationRef)

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Short answer:

ALTER PROCEDURE dbo.sp_Status_3

(

@myLocationRef int,

@myReviewerRef int =NULL,

@myBURef int =NULL

)

AS

SELECT *

FROM dbo.tmp_sp_Status_2

WHERE (ReviewerRef = COALESCE(@myReviewerRef,ReviewerRef)) AND (BURef = COALESCE(@myBURef, BURef)) AND (LocationRef = @myLocationRef)

Long answer:

This code works, but it is not designed to perform well. There's a problem with cached execution plans that can't be ignored.

To see the whole picture I suggest you take a look at this blog:

http://www.sommarskog.se/dyn-search-2005.html

Hope this helps

Gianluca

-- Gianluca Sartori

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

... or Gail's article

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

RunnerIE Say Hey Kid Points: 665 More actions · Answer 4

Matt / Gialuca / Ian:

Thanks for your very prompt responses.

In particular I have looked at the two articles identified by Gialuca and Ian and though tempted by the DynamicSQL option (see Search_Orders_2 in first article) I am sold on the idea of maintaining seperate stored procedures:

In my particular case I only have three seperate combinations of parameters being null (or not) and like keeping with an optimsed / stable plan to keep the speed up: I have a 'need for speed' and seperate procedures is a small price to pay.

'Long Answer' wins this one 🙂

Thanks !

Brian.