IF with COALESCE - SQL Server Forums

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

Transact-SQL (2005)

IF with COALESCE

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2011-05-15 : 08:55:17

I have a stored procedure. I need to check for conditions where each possible combinations for passed parameter could be passed as "ALL", 2 of the parmas passed as "ALL" string or 1 param (any of them) passed as string. The reason why I am doing this beacuse i am not able to do this from .net side. I have a dropdown list that has as one of the list items "ALL". Want to use the power of COALESCE to do search on null values. Right now only the first search condiction with ALL works. (only 1 dropdownlist with selected ALL will return results ). Maybe I need CASE statements?
ALTER PROCEDURE [dbo].[sp_SHOW_APPROVALS_WITH_COALESCE]

@REQUESTOR varchar(100) =NULL,
@REQUEST_TYPE_T varchar(100) =NULL,
@APPROVED_T varchar(100) =NULL

AS

BEGIN

IF @REQUESTOR = 'ALL'
SET @REQUESTOR = NULL
ELSE IF

@REQUEST_TYPE_T = 'ALL'
SET @REQUEST_TYPE_T = NULL
ELSE IF

@APPROVED_T = 'ALL'
SET @APPROVED_T = NULL

end

SELECT *
FROM PRIME_MASTER_APPROVALS
WHERE
REQUEST_TYPE_T = COALESCE(@REQUEST_TYPE_T,REQUEST_TYPE_T) AND
REQUESTOR = COALESCE(@REQUESTOR,REQUESTOR) AND
APPROVED_T = COALESCE(@APPROVED_T,APPROVED_T)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-15 : 09:08:24

Easier to do this:

ALTER PROCEDURE [dbo].[sp_SHOW_APPROVALS_WITH_COALESCE]

@REQUESTOR varchar(100) =NULL,
@REQUEST_TYPE_T varchar(100) =NULL,
@APPROVED_T varchar(100) =NULL

AS

BEGIN

SELECT @REQUESTOR = NULLIF(@REQUESTOR,'ALL'),
@REQUEST_TYPE_T = NULLIF(@REQUEST_TYPE_T,'ALL'),
@APPROVED_T = NULLIF(@APPROVED_T,'ALL')

SELECT *
FROM PRIME_MASTER_APPROVALS
WHERE
REQUEST_TYPE_T = COALESCE(@REQUEST_TYPE_T,REQUEST_TYPE_T) AND
REQUESTOR = COALESCE(@REQUESTOR,REQUESTOR) AND
APPROVED_T = COALESCE(@APPROVED_T,APPROVED_T)

Be advised that using the COALESCE technique will not give the best performance.

jszulc
Yak Posting Veteran

66 Posts

Posted - 2011-05-15 : 09:35:24

Much apprecizted, it works n ow.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-18 : 18:07:53

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources