Strange CASE

Question

Post reply

Strange CASE

Abhijeet Chavan

SSCommitted

Points: 1511
More actions
December 22, 2010 at 10:05 pm

#232282

I came across a problem.
Declare @tab1 table (ID int, Name varchar(10))
insert into @tab1 values (1, 'One')
insert into @tab1 values (2, 'Two')
insert into @tab1 values (3, 'Three')
Declare @Var1 int
set @var1 = 6
IF @var1 = 1
select * from @tab1
where ID in (1,2)
else
select * from @tab1
I want something like this
select * from @tab1
where ID = case when @var1 = 1 then (in (1,2)) else ID end
Thanks in advance

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

roi.reuven SSCommitted Points: 1700 More actions · Answer 1

for your example you can write

select * from @tab1

where (@var1 <> 1) or (id in (1,2))

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 2

Thanks for the reply

But sorry i didn't gave a complete idea of the problem

The problem is like this

Declare @tab1 table (ID int, Name varchar(10))

insert into @tab1 values (1, 'One')

insert into @tab1 values (2, 'Two')

insert into @tab1 values (3, 'Three')

Declare @Var1 Varchar(20)

set @var1 = 'ALL'

IF @var1 <> 'ALL'

select * from @tab1

where ID in @var1

else

select * from @tab1

I want something like this

select * from @tab1

where ID = case when @var1 <> 'ALL' then (in (@var1)) else ID end

roi.reuven SSCommitted Points: 1700 More actions · Answer 3

you need to first create the function

create Function [dbo].[fnSplitter] (@IDs Varchar(8000) )

Returns @Tbl_IDs Table (ID Int) As

Begin

-- Append comma

Set @IDs = @IDs + ','

-- Indexes to keep the position of searching

Declare @Pos1 Int

Declare @pos2 Int

-- Start from first character

Set @Pos1=1

Set @Pos2=1

While @Pos1<Len(@IDs)

Begin

Set @Pos1 = CharIndex(',',@IDs,@Pos1)

Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)

-- Go to next non comma character

Set @Pos2=@Pos1+1

-- Search from the next charcater

Set @Pos1 = @Pos1+1

End

Return

End

then you write

Declare @tab1 table (ID int, Name varchar(10))

insert into @tab1 values (1, 'One')

insert into @tab1 values (2, 'Two')

insert into @tab1 values (3, 'Three')

Declare @Var1 varchar(max)

set @var1 = '1,2'

select * from @tab1

where (@var1='0') or id in (select id from dbo.fnSplitter(@var1))

set @var1 = '0'

select * from @tab1

where (@var1='0') or id in (select id from dbo.fnSplitter(@var1))

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 4

Thank you very much for the solution.

Sometimes there is a very easy solution for a complicated problem and we just cant think of it

Thanks again.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

You can use Dynamic SQL for this issue. But since Table Variables defined outside the SQL statement cannot be used in the SQL statement, you will have to use a Temporary table or a physical table.

IF OBJECT_ID( 'tempdb..#tmp_Table' ) IS NOT NULL

DROP TABLE #tmp_Table

CREATE TABLE #tmp_Table( ID INT, Name VARCHAR(10) )

DECLARE@strSQL NVARCHAR(1000)

INSERT INTO #tmp_Table VALUES (1, 'One')

INSERT INTO #tmp_Table VALUES (2, 'Two')

INSERT INTO #tmp_Table VALUES (3, 'Three')

DECLARE @Var1 VARCHAR(20)

SET @var1 = 'ALL'

SET@strsql = 'SELECT* '

+ 'FROM#tmp_Table '

+ 'WHERE ' + CASE WHEN @var1 = 'ALL' THEN '1 = 1' ELSE 'ID IN ( ' + @var1 + ' ) ' END

EXECUTE sp_executeSQL @strsql

IF OBJECT_ID( 'tempdb..#tmp_Table' ) IS NOT NULL

DROP TABLE #tmp_Table

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Abhijeet Chavan SSCommitted Points: 1511 More actions · Answer 6

Hi kingston

Thanks for the reply

Actually the whole idea of my posting this problem was to not to use dynamic SQL.

Thanks again

roi.reuven SSCommitted Points: 1700 More actions · Answer 7

roi.reuven

SSCommitted

Points: 1700

December 22, 2010 at 11:41 pm

#1265785

Abhijeet

Thanks for the feedback.

GSquared SSC Guru Points: 260824 More actions · Answer 8

CELKO (12/27/2010)
Common mistake, made by newbies who did not finsih readign their first SQL book 😀
SQL has a CASE expression and think it is a CASE statement. Expressions return scalar values of a known data type. SQL is declarative and has no flow of control.

Common mistake, made by people who didn't finish reading their first English spelling book. (Sorry, Joe, I couldn't resist.)

T-SQL does have some limited flow control (IF, WHILE), but CASE gets a lot of people. Coming out of a VB background, it threw me for a bit of a ... loop. 🙂

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

One additional comment here: that is a VERY inefficient string splitting function. Search the SSC forums for a marvelous string splitting thread with incredible code samples and even benchmarking.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service