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 2000 Forums
 Transact-SQL (2000)
 Dynamic Where Clause one of 2 ways?

Author  Topic 

Crafty
Starting Member

3 Posts

Posted - 2008-03-12 : 10:25:29
Hi All,
Herewith my very 1st post...
I have a situation where I need to use a flag in a proc to either enable or disable 1 line in the where clause. The actual version has about 6 or 7 joins with 9 parameters, but for now
Now a VERY basic version follows...


CREATE TABLE [Test]
(
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[TName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Descr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsActive] [tinyint] NOT NULL CONSTRAINT [DF_Location_IsActive] DEFAULT (1),
[IsSystem] [bit] NOT NULL CONSTRAINT [DF_Location_IsSystem] DEFAULT (0),
[IsDepo] [bit] NOT NULL CONSTRAINT [DF_Location_IsDepo] DEFAULT (0),
)

INSERT INTO [Test]
(
[TName]
, [Descr]
, [IsActive]
, [IsSystem]
, [IsDepo]
)
VALUES
(
'Warehouse'
, 'On Site Warehouse'
, 1
, 0
, 0
)

INSERT INTO [Test]
(
[TName]
, [Descr]
, [IsActive]
, [IsSystem]
, [IsDepo]
)
VALUES
(
'CapeTown'
, 'CPT Warehouse'
, 1
, 0
, 1
)

INSERT INTO [Test]
(
[TName]
, [Descr]
, [IsActive]
, [IsSystem]
, [IsDepo]
)
VALUES
(
'Durban'
, 'Harbour Holding'
, 1
, 0
, 0
)

GO

Create procedure sp_test1
@TName varchar(50)
,@ReturnAll as bit

as
--exec sp_test1 @TName='Durban', @ReturnAll = 0
--exec sp_test1 @TName=null, @ReturnAll = 1

select *
from Test
where
TName = @Tname


The idea is that if you send a 1 value to the bit parameter, then the procedure should run without a where clause.
One way i thought of doing it is to have 2 seperate code blocks. Then you test in the begining of the proc for the bit. if true, you exec the one block, if false the other. Then the only difference between the 2 code blocks is the where clause.
But in my mind this does not seem like best practice. I'm sure there is a better way to do this.
I just started this job, and I still eed to impress them... ;-)

Only some can learn from other peoples mistakes. The rest of us has to be the other people...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-12 : 10:29:25
how about...

where @returnall = 1 or (@returnall = 0 and tname = @tname)


Em
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-12 : 10:50:30
quote:

how about...

where @returnall = 1 or (@returnall = 0 and tname = @tname)



even shorter:

where @returnall = 1 or tname = @tname


- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Crafty
Starting Member

3 Posts

Posted - 2008-03-12 : 17:23:02
quote:
Originally posted by elancaster

how about...

where @returnall = 1 or (@returnall = 0 and tname = @tname)


Em



Thanx!!!!
This worked like a charm. I used this option, since I have a bunch of other clauses in there as well.


Only some can learn from other peoples mistakes. The rest of us has to be the other people...
Go to Top of Page
   

- Advertisement -