Dynamic IN to find resilts

Question

Post reply

Dynamic IN to find resilts

ramrajan

SSCommitted

Points: 1737
More actions
June 2, 2014 at 6:36 am

#294070

I have a query
declare @leveltype varchar(100)
set @leveltype = '1,2,3'
select *
FROM DefaultLevelNames where leveltype in ( select [values] from Split(@leveltype,',') )
Here is the result set for the query
LanguageIdDefaultLevelIdLevelTypeLevelName
111Parent
122Region
133Sub-Region
How can i get all rows from the table if my variable @leveltype = '' Please help me

Viewing 4 posts - 1 through 3 (of 3 total)

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

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

Because you already have some logic in the script, why not apply some extra logic to determine the query you need:

declare @leveltype varchar(100)

set @leveltype = '1,2,3'

IF @leveltype = ''

select *

from DefaultLevelNames

ELSE

select *

from DefaultLevelNames

where leveltype in (select [values]

from Split(@leveltype,',')

)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ramrajan SSCommitted Points: 1737 More actions · Answer 2

i have just given one example , but in my real cases there are 100's of tables which need this so looking for some generic solution that can be applies for all.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

ramrajan (6/2/2014)
I have a query
declare @leveltype varchar(100)
set @leveltype = '1,2,3'
select *
FROM DefaultLevelNames where leveltype in ( select [values] from Split(@leveltype,',') )
Here is the result set for the query
LanguageIdDefaultLevelIdLevelTypeLevelName
111Parent
122Region
133Sub-Region
How can i get all rows from the table if my variable @leveltype = '' Please help me

For this one example, and this is a catch all query, you could do this:

declare @leveltype varchar(100)

set @leveltype = '1,2,3'

select

*

FROM

DefaultLevelNames

where

leveltype in ( select [values] from Split(@leveltype,',') ) or @leveltype = '';