SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query quesiton


Query quesiton

Author
Message
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4285 Visits: 2774
how can i achieve this?
Declare @p1 = 'xyz,tln,tprq'

select * from table1 where col1 in('xyz','tln','tprq')
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9872 Visits: 13350
Use a split function: http://www.sqlservercentral.com/articles/Tally+Table/72993/

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Steve Hall
Steve Hall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2822 Visits: 11564
As Gianluca has shown, one way to do this is to split @P1, because it is no use in it's current format as a parameter.
Col1 would have to have a value of 'xyz,tln,tprq' in one row.

Another option is to use Dynamic SQL, but this would require a change in the format of @P1 - this may be an option for you.
Pay attention to the number of single quotes required in the value of @P1:

CREATE TABLE #Test1(
Test_Text VarChar(20)
);

INSERT INTO #Test1(Test_Text)
VALUES('abc'),
('cde'),
('fgh'),
('xyz'),
('123'),
('tln'),
('456'),
('tprq');

DECLARE @P1 VarChar(30);

SET @P1 = '''xyz'''+','+'''tln'''+','+'''tprq''';

SELECT @P1;

EXEC(N'SELECT * FROM #Test1 WHERE Test_Text IN (' + @P1 + ')');



Steve Hall
Linkedin
Blog Site
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4285 Visits: 2774
create table #t1
(id varchar(5))

insert into #t1 values('xyz'), ('kkl'), ('tln'), ('cxz'), ('dfa'), ('tprq')

select * from #t1

Declare @p1 varchar(50) = 'xyz,tln,tprq'
Declare @p2 varchar(50) = ''''+replace(@p1, ',', ''',''')+''''
select @p1
select @p2

select * from #t1 where id in(@p2)

Why teh last select does not return any value inspite of @p2 having the correct presentation of condition ?
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14175 Visits: 15963
Because, as Gianluca and BrainDonor have already mentioned, you need to use a split function or dynamic SQL. Make sure you understand the implications of the latter.

John
Steve Hall
Steve Hall
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2822 Visits: 11564
Because you need to run it as Dynamic SQL, using the EXEC command:

EXEC(N'SELECT * FROM #Test1 WHERE Test_Text IN (' + @P1 + ')');



What you have is SQL probably attempting to find a match to the entire varchar ''xyz','tln','tprq'', not three seperate values.

Steve Hall
Linkedin
Blog Site
sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4285 Visits: 2774
I agree but why does not it works like this ? I mean is there something more precise or some doc where I can get deep understanding on this ?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87429 Visits: 45272
Because select * from #t1 where id in(@p2) is completely and totally equivalent to select * from #t1 where id = @p2. Each item in an IN (variable, parameter or constant) is considered to be a single value

To specify variables in an IN, it would be like this: select * from #t1 where id in(@p1, @p2, @p3, @p4)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sqlnaive
sqlnaive
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4285 Visits: 2774
Thanks Gail, I got it from your point. Thanks to others as well for describing.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search