Select ALL if Var is NULL - 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

General SQL Server Forums

New to SQL Server Programming

Select ALL if Var is NULL

Author

Topic

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-21 : 13:43:12

How would amend the following so that if one or both variables were NULL the result would default of ALL (everything)for that variable

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[InLnFncSchool]
(
@School nvarchar(100) =NULL,
@Closed nvarchar(3) = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT *
FROM TblSchool
WHERE SchoolName = @School AND
Closed = @Closed
);

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-11-21 : 13:52:30

WHERE SchoolName = coalesce(@School, SchoolName)
AND Closed = coalesce(@Closed, Closed)

Be One with the Optimizer
TG

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-21 : 13:59:59

Test and works great - That's brilliant, Thanks loads

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-21 : 14:07:12

That is what we call a "catch-all" query. Here is a link that has more detail and performance trade offs.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-22 : 12:39:41

@Lamprey

Thanks for the heads-up

so in this case, without re-writing the above query string dynamically how would I use sp_recompile, would it be something like

ALTER FUNCTION sp_recompile [dbo].[InLnFncSchool] ...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-22 : 13:00:19

That is not how you use sp_recompile:

sp_recompile [ @objname = ] 'object'

But, why would you use sp_recompile or what is your goal?

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-22 : 13:08:58

Just reading through your link re "catch-all" query and also having just starting off on SQL (about 5 days now) the article, which maybe I should read again, suggests 2 ways of improving performance for Catch-All and sp_recompile is one of them. did I misunderstand it? I wouldn't be surprised if I did?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-22 : 13:11:37

Maybe you mean the OPTION (RECOMPILE) hint? If so, that might be worth looking into, but make sure you test, test and test again to see how it affects your performance.

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-22 : 13:19:17

thanks, will read-up tonight

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources