Join Parameter Problem

Question

Post reply

Join Parameter Problem

Kingsleen Solomon Doss

Old Hand

Points: 340
More actions
July 1, 2008 at 5:43 am

#96496

hi
The Parameter i am getting is (Name1,NAme2,Name3) I need to join the parameter as ('Name1','Name2',Name3') how can i do in sql please help me out
This is my query
Declare @Ccode varchar(20)
set @Ccode=(C022,C007,C044,C050,C020)
print @Ccode
select * from ReconciledSites
where Ccode in (@Ccode)

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

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

abhijeetv SSCommitted Points: 1945 More actions · Answer 1

Actually yoiu need to develop a custom Split function

you can refer -

http://www.sqlservercentral.com/Forums/Topic129079-150-1.aspx?Highlight=fnSplit

noeld SSC Guru Points: 96590 More actions · Answer 2

Kingsleen Solomon Doss (7/1/2008)
hi
The Parameter i am getting is (Name1,NAme2,Name3) I need to join the parameter as ('Name1','Name2',Name3') how can i do in sql please help me out
This is my query
Declare @Ccode varchar(20)
set @Ccode=(C022,C007,C044,C050,C020)
print @Ccode
select * from ReconciledSites
where Ccode in (@Ccode)

OR:

Declare @Ccode varchar(20)

set @Ccode= 'C022,C007,C044,C050,C020'

print @Ccode

exec ('select * from ReconciledSites where Ccode in ( ' + @Ccode + ' ) '

There are all sorts of issues with dynamic sql I would recommend you to check for those. In the mean time the above is a quick and dirty solution.

* Noel

sgmunson SSC Guru Points: 110640 More actions · Answer 3

Unless there's some reason to avoid using the LIKE operator, I think a rather minor recoding of the original will work just fine:

Declare @Ccode varchar(20)

set @Ccode='C022,C007,C044,C050,C020'

print @Ccode

select * from ReconciledSites

where @Ccode LIKE '%' + Ccode + '%'

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

GSquared SSC Guru Points: 260824 More actions · Answer 4

The reason to avoid the Like operator in that is because it is immensely slower than splitting the list (use a Numbers table for that), and joining to that. Test both, use IO and Time statistics, you'll see what I mean.

- 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