Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query quesiton


Query quesiton

Author
Message
sqlnaive
sqlnaive
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

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

select * from table1 where col1 in('xyz','tln','tprq')
spaghettidba
spaghettidba
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

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

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 11186
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 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
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7425 Visits: 15117
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
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 11186
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47213 Visits: 44370
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
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3683 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