CASE Statement and Null value - Some records do not show up.

CASE Statement and Null value - Some records do not show up.

Post by Marco Napo » Sun, 17 Oct 2004 02:34:27


I am using a CASE Statement in the WHERE clause in order to filter by a
certain salesperson_id or by ALL.

WHERE (CASE WHEN 'ALL' = @mSalespersonALL THEN salesperson_id ELSE
@mSalespersonId END) = salesperson_id

------------------------------------------------------------------------------
Find below my DDL and SELECT Statement:

-- start of DDL
CREATE TABLE [dbo].[TestContact] (
[contact_id] [decimal](18, 0) IDENTITY (300000, 1) NOT NULL ,
[salesperson_id] [numeric](18, 0) NULL ,
[contact] [varchar] (254) NULL
) ON [PRIMARY]
GO

CREATE INDEX [contact_id] ON [dbo].[TestContact]([contact_id]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [salesperson_id] ON [dbo].[TestContact]([salesperson_id]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO

INSERT INTO TestContact VALUES (1,'John')
INSERT INTO TestContact VALUES (2,'Cindy')
INSERT INTO TestContact VALUES (3,'Wanda')
INSERT INTO TestContact VALUES (Null,'Does not show in SQL')
-- end of DDL

-- Trying to get All records or selected SalespersonID
DECLARE @mSalespersonALL VarChar
DECLARE @mSalespersonID INTEGER

SET @mSalespersonALL = 'ALL'
SET @mSalespersonID = 0

SELECT *
FROM TestContact
WHERE (CASE WHEN 'ALL' = @mSalespersonALL THEN salesperson_id ELSE
@mSalespersonId END) = salesperson_id

------------------

Thanks and God Bless
Marco Napoli
http://www.yqcomputer.com/
 
 
 

CASE Statement and Null value - Some records do not show up.

Post by QWxlamFuZH » Sun, 17 Oct 2004 02:59:07

If you will not allow @mSalespersonALL <> 'ALL' and @mSalespersonId IS NULL,
then:

SELECT *
FROM TestContact
WHERE @mSalespersonALL = 'ALL' or salesperson_id = @mSalespersonId

Dynamic Search Conditions in T-SQL
http://www.yqcomputer.com/



AMB

 
 
 

CASE Statement and Null value - Some records do not show up.

Post by Marco Napo » Sun, 17 Oct 2004 03:36:54

Thanks Alejandro.

Its not doing what I needed to do. My problem is that I need to create in
the WHERE clause the two below scenarios into one SQL Statemet

Filter just by salesperson_id:
SELECT *
FROM TestContact
WHERE salesperson_id = @mSalespersonId

Filter by ALL:
SELECT *
FROM TestContact


But I only know at runtime if the user picks form a Selection Box the Filter
ALL or by SalespersonId value.

Thanks
Marco
 
 
 

CASE Statement and Null value - Some records do not show up.

Post by QWxlamFuZH » Sun, 17 Oct 2004 04:43:10

arco,

I forgot to mention that you declared variable @mSalespersonALL as varchar
without specifing length. SQL Server will assume 1.


if you print the value of SET @mSalespersonALL you will see "A" that is why
the expression:


is not working.



AMB

"Marco Napoli" wrote:

 
 
 

CASE Statement and Null value - Some records do not show up.

Post by R-D- » Sun, 17 Oct 2004 05:09:07

When selecting from a table, the where clause is after Boolean results but
every item does not need to include a table column. If I understand you
correctly that you aer checking @mSalespersonALL for 'ALL' and if it is
'ALL' you want every row otherwise you only want the row identified by
@mSalespersonId then the following should work.

SELECT *
FROM TestContact
WHERE (salesperson_id = @mSalespersonId)
OR (@mSalespersonALL='ALL')
 
 
 

CASE Statement and Null value - Some records do not show up.

Post by Marco Napo » Sun, 17 Oct 2004 06:40:57

Thank you so much.

Marco
 
 
 

CASE Statement and Null value - Some records do not show up.

Post by John Do » Sun, 17 Oct 2004 07:12:16

Marco,

Try this:


declare @sp_id varchar(50)
set @sp_id = 'All'
-- replace ALL by 1
declare @sp_id_temp varchar(10)
if @sp_id = 'All'
begin set @sp_id_temp = '%' end
else
begin set @sp_id_temp = @sp_id end

SELECT *
FROM TestContact
WHERE salesperson_id like @sp_id_temp


Hope this helps!



*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
 

CASE Statement and Null value - Some records do not show up.

Post by Uks » Sat, 23 Oct 2004 14:49:07

Marco,

If I have understood your question correctly, use the UNION or UNION ALL
statement and if this query is frequently used, create it as a VIEW or sp.

Regards

Ram.