CASE Statement help for No Data

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

CASE Statement help for No Data

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-04-05 : 16:02:41

Hi
I have this below CASE Statement which is not returning the value I'm expecting when there is no data.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SHOWASSIGN]
(
@RQID int
)
AS
Select distinct ReqNo = 
CASE

When reqno is Null then 'N'
When reqno != 0 then 'Y'END
FROM Customer_Attachments
WHERE (reqno = @RQID)

I want 'N' to return when there is no data for a reqno in the customer_attachments table.I get it right when there is data but it returns nothing when there is no data for a request

What am i doing wrong?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 16:15:02

if you're looking for presence of records make it like


Select ReqNo = 
CASE

When Cnt = 0 then 'N'
else 'Y'END
FROM (SELECT COUNT(*) AS Cnt FROM Customer_Attachments
WHERE (reqno = @RQID))t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-04-06 : 08:27:35

Thank You

quote:
Originally posted by visakh16

if you're looking for presence of records make it like

Select ReqNo =
CASE

When Cnt = 0 then 'N'
else 'Y'END
FROM (SELECT COUNT(*) AS Cnt FROM Customer_Attachments
WHERE (reqno = @RQID))t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 09:04:04

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources