Can I use both coalesce and inline_split_me? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

Can I use both coalesce and inline_split_me?

Author

Topic

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-05-20 : 22:40:46

Hi All,

I am trying to use Coalesce (in case @str is null) and inline_split_me ( because @str is an Array)

declare @str varchar(100), @city varchar(100)
SET @str= '121707,121708,121709'
SET @city= 'Tokyo'

CREATE FUNCTION dbo.ShowRecords (@str, @city)
select etsid from eTicketMain where city=@city and etsid in (
select * from inline_split_me(
coalesce( @str, etsid )
))

The purpose of having Coalesce is that If @str=Null, all the records that is city=@city will show. If @str is not Null, then only the selected records ( records that are in etSID and city=@city) will show.

Please advise.

Thanks

**Jonathan**

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-20 : 22:49:43

try this


select t.etsid
from   eTicketMain t inner join inline_split_me(@str) c
on     t.etsid = c.str
or     @str    is null
where  t.city = @city

KH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 00:36:29

Also make sure if ShowRecords is SP or Function

Madhivanan

Failing to plan is Planning to fail

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-05-21 : 01:47:16

quote:

Originally posted by khtan

try this


select t.etsid
from   eTicketMain t inner join inline_split_me(@str) c
on     t.etsid = c.str
or     @str    is null
where  t.city = @city

KH

If I would like to add more condtions at the where clause, how should I do it?

**Jonathan**

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-21 : 01:52:15

add in the WHERE clause


select t.etsid
from   eTicketMain t inner join inline_split_me(@str) c
on     t.etsid = c.str
or     @str    is null
where  t.city = @city
and    <more condition>

KH

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-05-22 : 01:58:09

quote:

Originally posted by khtan

try this


select t.etsid
from   eTicketMain t inner join inline_split_me(@str) c
on     t.etsid = c.str
or     @str    is null
where  t.city = @city

KH

hmm....I tried that. It seems that when @str is null then no result is displayed because of the or@str is Null . That is not the desired outcome. The desired outcome should be all the result should be displayed with t.city = @city and etsid = any value

please advise

**Jonathan**

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 02:35:13

post your table DDL, some sample data and the desire result

KH

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-05-22 : 04:59:25

declare @str varchar(100)
SET @str= '121707,121708,121709' <---case 1
SET @str= Null < ---- case 2:disable this one if you want case 1
declare @city varchar(100)
SET @city= 'TPE'
select t.etsid
from eTicketMain t inner join inline_split_me(@str) c
on t.etsid = c.value
or @str is null
where t.eTDeparture = @city

Data
etsid eTDeparture
121701 KKD
121703 TPE
121704 TPE
121705 TPE
121706 TPE
121707 TPE
121708 TPE
121709 TPE

case1 : Desired result
etsid
121707
121708
121709

case2 : Desired result
121703
121704
121705
121706
121707
121708
121709

**Jonathan**

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-22 : 05:29:50

try this..

select t.etsid
from eTicketMain t
where t.eTDeparture = @city and
( ',' + @str + ',' like '%,' +t.etsid +',%' or @str is null)

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-05-22 : 05:55:24

hmm....what if in the future the etSID number increases and it may be a problem by using like statement?

**Jonathan**

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 06:04:18

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-22 : 06:05:57

may i know what ur doing inside the inline udf..

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-05-22 : 06:34:44

I modify my code slightly and i got the solution

select etsid from eTicketMain where city=@city and
etsid in (select * from inline_split_me(@str)) or <---condition 1
etsid=coalesce( @str, etsid ) <----condition 2

so if @str is an array, condition 1 will be true and condition 2 will get not result
if @str is sinlge number such as 121707, condition 1,2 will get the result
it @str is null then condition 1 will get no result and condition2 will be etsid=etsid

**Jonathan**

wangyc77
Yak Posting Veteran

65 Posts

Posted - 2007-05-22 : 06:36:59

quote:
Originally posted by pbguy

may i know what ur doing inside the inline udf..

a very cool udf to use I learn it from SQLTeam.com
this is the reference
http://www.sommarskog.se/arraylist-2005/tblnum-functions.html

**Jonathan**

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources