Ignore NULL StoredProcedure parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Ignore NULL StoredProcedure parameters

Author

Topic

josephl
Starting Member

12 Posts

Posted - 2008-03-18 : 20:50:32

Hi,
I do have a stored procedure with parameters inside. I just want to ignore those parameters that are NULL so that my WHERE clause will not execute them anymore. Here's my code:

CREATE PROCEDURE SEARCHPATIENT
@patnCode varchar(10) = NULL,
@patnSurname varchar(20) = NULL,
@patnGivenName1 varchar(20) = NULL
AS
SELECT....
FROM ...
WHERE patnCode=@patncode AND
patnSurname =@patnSurname AND
patnGivenName1 =@patnGivenName1

BUT...
If the user passes @patnSurname as NULL, I got an SQL error message:
"There was an error executing the query.. Timeout expired... ". I was hoping I could write an WHERE clause where IT WILL ONLY EXECUTE those parameters WITH VALUE and IGNORE those NULLs.

Can someone help me on this?
Can I use IF THEN ELSE statement inside the WHERE clause?
Can I use CASE STATEMENT inside the WHERE clause?

Thanks in advance.

Joseph

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-18 : 21:50:26

Use COALESCE function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

josephl
Starting Member

12 Posts

Posted - 2008-03-18 : 22:08:42

Hi,
I already used it before - still getting either no records or SQL query error:

Here is my exact SP code. Hope you could help me.
thanks

========================
CREATE PROCEDURE SEARCHPATIENT
@patnCode varchar(10) = NULL,
@patnSurname varchar(20) = NULL,
@patnGivenName1 varchar(20) = NULL,
@patnDateOfBirth varchar(20) = NULL,
@patnSexCode varchar(20) = NULL
AS
DECLARE @tCat TABLE
(rowID int identity(1,1),patnGuid varchar(100), patnCode varchar(50),
patnSurname varchar(50),patnGivenName1 varchar(50),
patnDateOfBirth varchar(50),patnSexCode varchar(20),
patnAddressLine1 varchar(100),
patnAddressLine2 varchar(100),patnSuburb varchar(100),
patnCityTown varchar(100))
INSERT @tCat (patnGuid,patnCode,
patnSurname,patnGivenName1,
patnDateOfBirth,patnSexCode,
patnAddressLine1,
patnAddressLine2,patnSuburb,
patnCityTown)
SELECT patnGuid,patnCode,
patnSurname,patnGivenName1,
patnDateOfBirth,patnSexCode,
patnAddressLine1,
patnAddressLine2,patnSuburb,
patnCityTown
FROM Disp_PatNative
WHERE
((patnSurname IS NULL OR patnSurname LIKE COALESCE(@patnSurname, '%')) AND
(patnGivenName1 IS NULL OR patnGivenName1 LIKE COALESCE(@patnGivenName1, '%')) )

SELECT rowid, patnGuid,patnCode,
patnSurname,patnGivenName1,
CONVERT(VarChar, patnDateOfBirth, 103) AS DOB, CONVERT(Varchar, patnSexCode) AS SEX,
patnAddressLine1 + ' ' + patnAddressLine2 + ' ' + patnSuburb + ' ' + patnCityTown AS ADDRESS
FROM @tCat
ORDER BY rowid, patnSurname,patnGivenName1

GO

=======================

josephl
Starting Member

12 Posts

Posted - 2008-03-18 : 23:26:48

Hi tkizer,

I got it! I just manipulate the COALESCE function you've mentioned like this:

WHERE...
(patnSurname LIKE COALESCE(@patnSurname,patnSurname) + '%' AND
patnGivenName1 LIKE COALESCE(@patnGivenName1, patnGivenName1) + '%' AND
LEFT(patnSexCode,1) = COALESCE(LEFT(@patnSexCode,1) ,patnSexCode) AND
CONVERT(VarChar, patnDateOfBirth, 103) = COALESCE(@patnDateOfBirth,CONVERT(VarChar, patnDateOfBirth, 103))

THANKS A LOT!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources