If condition in select case - 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

Development Tools

ASP.NET

If condition in select case

Author

Topic

Swati Jain
Posting Yak Master

139 Posts

Posted - 2007-10-22 : 03:04:45

SELECT @Output = CASE @Output
WHEN '' THEN ReceiptsDetail.DocumentNumber WHEN 'Blank' THEN @Output+'' ELSE @Output + ', ' + ReceiptsDetail.DocumentNumber END

Actualy in the text marked in red I want that
if ReceiptsDetail.DocumentNumber ='Blank' then it should be excluded from @Output but how here I can write this condition

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2007-10-22 : 03:18:10

It seems you want two case condition. If yes, then you can use case within case. Or you can use something like this
select @output = case when @output='' and ReceiptsDetail.DocumentNumber ='Blank' then ...else ... end

Ramesh Singh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-22 : 04:15:02

SELECT @Output = @Output+ 
	CASE @Output ='' and ReceiptsDetail.DocumentNumber ='Blank' THEN '' 
		ELSE  ', ' + cast(ReceiptsDetail.DocumentNumber as varchar(20)) 
	END

Madhivanan

Failing to plan is Planning to fail

ChandanJ
Starting Member

6 Posts

Posted - 2007-10-29 : 14:00:27

try this

SELECT @Output = @Output +
CASE WHEN ReceiptsDetail.DocumentNumber = 'Blank' THEN ''
ELSE ReceiptsDetail.DocumentNumber
END

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-30 : 01:54:08

quote:
Originally posted by ChandanJ

try this

SELECT @Output = @Output +
CASE WHEN ReceiptsDetail.DocumentNumber = 'Blank' THEN ''
ELSE ReceiptsDetail.DocumentNumber
END

You need to convert the documentnumber to varchar if it is of INT datatype

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources