Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query quesiton Expand / Collapse
Author
Message
Posted Tuesday, March 06, 2012 12:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
how can i achieve this?
Declare @p1 = 'xyz,tln,tprq'

select * from table1 where col1 in('xyz','tln','tprq')
Post #1261985
Posted Tuesday, March 06, 2012 1:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:26 PM
Points: 4,929, Visits: 8,740
Use a split function: http://www.sqlservercentral.com/articles/Tally+Table/72993/

--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1261997
Posted Tuesday, March 06, 2012 2:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 1,533, Visits: 7,916
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 + ')');



BrainDonor
Linkedin
Blog Site
Post #1262002
Posted Friday, March 09, 2012 2:31 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
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 ?
Post #1264190
Posted Friday, March 09, 2012 2:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:58 AM
Points: 5,077, Visits: 8,918
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
Post #1264192
Posted Friday, March 09, 2012 2:46 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 7:48 AM
Points: 1,533, Visits: 7,916
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.



BrainDonor
Linkedin
Blog Site
Post #1264194
Posted Friday, March 09, 2012 3:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
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 ?
Post #1264202
Posted Friday, March 09, 2012 3:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #1264217
Posted Friday, March 09, 2012 5:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:04 AM
Points: 3,517, Visits: 2,606
Thanks Gail, I got it from your point. Thanks to others as well for describing.
Post #1264271
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse