Procedure with optional parameters

Question

Post reply

Procedure with optional parameters

Federico Beltran

Grasshopper

Points: 19
More actions
August 20, 2004 at 2:34 am

#181496

I have a Procedure for search clients. User can give name, lastname, phone and so on but some of those might be Null. The procedure receive the optional @parameters. I have use the following query
Select * FROM dbo.CLIENTS WHERE lastname = isnull(@prmlastname,lastname) and name=isnull(@prmname,name) and ... But of course will be more efficient some like:
IF @prmlastname in null and @prmname is not null select * from dbo.clientes where name=@name but if a have 5 parametres it could be as much as 25 IFs and that is a lot of work.
Other opcion Dinamic SQL but then I lost the precompiled caracteristic.
What is the best solution?
Is there any other solution for optional paramenters?
Thanks for your help

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

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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

The following example is straight out of "Books On Line"... modify it to handle your 5 nullable input parameters...

D. Create a stored procedure using a parameter default of NULL

The parameter default can be the value NULL. In this case, if you do not supply a parameter, SQL Server executes the stored procedure according to its other statements. No error message is displayed.

The procedure definition can also specify that some other action be taken if you do not give a parameter. For example:

CREATE PROC showind3 
       @table varchar(30) = NULL
AS 
   IF @table IS NULL
   PRINT 'Give a table name'
ELSE   
   SELECT TABLE_NAME = sysobjects.name,   
          INDEX_NAME = sysindexes.name, 
            INDEX_ID = indid   
     FROM sysindexes 
    INNER JOIN sysobjects   
            ON sysobjects.id = sysindexes.id   
    WHERE sysobjects.name = @table

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 2

Federico,

Read the following article by Erland Sommarskog, SQL Server MVP:

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

It's an in-depth article about this problem, with many solutions (using Dynamic SQL or Static SQL).

Razvan

John Jakob SSCommitted Points: 1720 More actions · Answer 3

This SEEMS to work, but no guarantees!

- john

USE Northwind

GO

-- Demonstrate A Query Using An Optional Parameter

CREATE PROCEDURE spGetOrders

@OrderID INT = NULL

AS

BEGIN

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM Orders

WHERE OrderID =

CASE

WHEN @OrderID IS NULL THEN OrderID

ELSE @OrderID

END

ORDER BY OrderID

END

GO

-- Test:

EXEC spGetOrders -- Returns All Rows

GO

EXEC spGetOrders 10258 -- Returns 1 Row

GO

John Jakob SSCommitted Points: 1720 More actions · Answer 4

(Update--continued from last post) Actually, I vaguely recall having "gotten burned" by some weird NULL-handling behavior in a query like this. Things didn't always perform as expected.

Instead of testing against NULL, it might be safer if you hard-code a value for the optional parameter that you KNOW will never appear in the data -- e.g.,

-- SAMPLE #2: Demonstrate A Query Using An Optional Parameter

CREATE PROCEDURE spGetOrders2

@OrderID INT = -9999

AS