Query quesiton

Question

Post reply

Query quesiton

sqlnaive

SSCoach

Points: 17437
More actions
March 6, 2012 at 12:47 am

#104132

how can i achieve this?
Declare @p1 = 'xyz,tln,tprq'
select * from table1 where col1 in('xyz','tln','tprq')

Viewing 9 posts - 1 through 8 (of 8 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Use a split function: http://www.sqlservercentral.com/articles/Tally+Table/72993/

-- Gianluca Sartori

BrainDonor SSCoach Points: 19254 More actions · Answer 2

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_TextVarChar(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 SSCoach Points: 17437 More actions · Answer 3

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 SSC Guru Points: 148809 More actions · Answer 4

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 SSCoach Points: 19254 More actions · Answer 5

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 SSCoach Points: 17437 More actions · Answer 6

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 ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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 SSCoach Points: 17437 More actions · Answer 8

Thanks Gail, I got it from your point. Thanks to others as well for describing.