Using Set Variable On/Off - 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

Using Set Variable On/Off

Author

Topic

yogi86
Starting Member

13 Posts

Posted - 2011-08-24 : 10:26:45

Hello I would like to use set variables only when needed. I have them set but sometimes I do not want to use them and want the query to run the results without using them. Yet I do not want to go through the query to comment them out.

Here is the partial query.

--INPUT VARIABLES HERE--

-- WHAT IS END OFFICE NAME?
set @switch = 'KSCBMO35DS0' -- <---- Enter Information
-- WHEN IS THE BEGINNING OF PERIOD?
set @beginningdate = '20110501' -- <---- Enter Information
-- WHEN IS THE END OF PERIOD?
set @enddate = '20110531' -- <---- Enter Information
--IS IT ITE (1) OR ITA (2)
set @ita_ite = 1 -- <---- Enter Information
-- IS IT ORIG OR TERM?
set @term_orig = 'TERM' -- <---- Enter Information

-- DO NOT GO BEYOND THIS LINE --

set nocount on

set @ocn = (select top 1 OCN
from pricing..netplan_tailoredlerg (nolock)
where @switch = switch)

set @NPA = (select top 1 NPA
from pricing..netplan_tailoredlerg (nolock)
where @switch = switch)

set @NXX = (select top 1 NXX
from pricing..netplan_tailoredlerg (nolock)
where @switch = switch)

--ORIG
set @origswitch = (select top 1 [H-ORG_D_TDM]
from pricing..netplan_tailoredlerg (nolock)
where @switch = switch and sha_indicator='00')

So my ideal query would run by searching all 'switch' in the event I do not set @switch to a variable.

Is this possible?

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 10:54:36

WHERE (@switch IS NULL OR @switch = switch)

Thus if you set the @switch to NULL if will have no effect, if you set it to a value then that must match the column

Usually helpful if you have multiple, optional, selection criteria:


WHERE     (@Param1 IS NULL OR @Param1 = MyColumn1)
      AND (@Param2 IS NULL OR @Param2 = MyColumn2)
      ...

Kristen
Test

22859 Posts

Posted - 2011-08-24 : 10:55:47

Don't like the look of those NOLOCK commands. Usually means bad news ...

... look at setting the database to READ_COMMITTED_SNAPSHOT instead.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-24 : 12:27:46

Just be aware, the "WHERE (@Param1 IS NULL OR @Param1 = MyColumn1) AND (@Param2 IS NULL OR @Param2 = MyColumn2) AND ..." pattern is an almost guarantee of poor performance.

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

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources