IN function

Question

Post reply

IN function

Q-Girl

Valued Member

Points: 66
More actions
April 8, 2010 at 11:13 am

#222936

Hey all,
I'm trying to use the IN function in a where clause like this:
where screen_name in('MN','KM')
but instead of the literals I want to use a variable:
where screen_name in(@tech_list)
Anybody out there know the proper syntax for the contents of the variable? Or if you can even do this?
Thanks,
Jude

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

You would need to use dynamic SQL for that:

Here's an example:

DECLARE

@list VARCHAR(30),

@SQLString NVARCHAR(200)

SET @list ='''Bothell'',''Portland'''

SET @SQLString = N'SELECT * FROM Person.Address WHERE city in('+@list+')'

EXEC sp_executesql @SQLString

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323547 More actions · Answer 2

you have to use a split function which changes the string @tech_list into a table function. otherwise you are trying use IN on a string which just happens to contain commas. the IN has to be either hardcoded values, or a SELECT from a table; the hardcoded values can't be a string.

the usage then is going to look like this:

SELECT *

FROM YourTable

where screen_name in(select item from [dbo].[DelimitedSplit] (@tech_list,',') --split by comma

here's one of many versions you can find here on SSC by searching the script contributions.

CREATE function [dbo].[DelimitedSplit] (

@pString varchar(max),

@pDelimiter char(1)

)

returns table

as

return

with

a1 as (select 1 as N union all select 1 union all

select 1 union all select 1 union all

select 1 union all select 1),

a2 as (select 1 as N from a1 as a cross join a1 as b),

a3 as (select 1 as N from a2 as a cross join a2 as b),

a4 as (select 1 as N from a3 as a cross join a2 as b),

Tally as (select top (len(@pString)) row_number() over (order by N) as N from a4),

ItemSplit(ItemOrder,Item) as

(

SELECT

N,

SUBSTRING(@pDelimiter + @pString + @pDelimiter,N + 1,CHARINDEX(@pDelimiter,@pDelimiter + @pString + @pDelimiter,N + 1) - N - 1)

FROM Tally

WHERE N < LEN(@pDelimiter + @pString + @pDelimiter)

AND SUBSTRING(@pDelimiter + @pString + @pDelimiter,N,1) = @pDelimiter --Notice how we find the delimiter

)

select

row_number() over (order by ItemOrder) as ItemID,

Item

from ItemSplit

Q-Girl (4/8/2010)
Hey all,
I'm trying to use the IN function in a where clause like this:
where screen_name in('MN','KM')
but instead of the literals I want to use a variable:
where screen_name in(@tech_list)
Anybody out there know the proper syntax for the contents of the variable? Or if you can even do this?
Thanks,
Jude

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95461 More actions · Answer 3

You basicly have two options:

1. Use dynamic sql @cmd = '... IN(' + @tech_list + ')'

2. Use a string splitting routine to make separate rows out of each entry, and join to that. This example utilizes a virtual tally table; see the referenced article for how to build/use one. Then join the results of this to your table.

declare @list TABLE (MixedData varchar(50));

insert into @list select 'AB,CD,EF,GH,IJ,KL,MN,OP,QR,ST,UV,WZ,YZ';

-- first, need to break down each mixed data row into separate items.

-- See Jeff Modem's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

-- at http://www.sqlservercentral.com/articles/T-SQL/62867/ for how a tally table can split strings apart.

WITH Tens (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),

Hundreds AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2),

Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Hundreds)

SELECT RTRIM(LTRIM(SUBSTRING(',' + MixedData + ',',N+1,CHARINDEX(',',',' + MixedData + ',',N+1)-N-1)))

FROM Tally, @list

WHERE N < LEN(',' + MixedData + ',')

AND SUBSTRING(',' + MixedData + ',',N,1) = ','

Edit: whoops... I see while I was busy composing this, Lowell and Lutz both answered this.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Q-Girl Valued Member Points: 66 More actions · Answer 4

Q-Girl

Valued Member

Points: 66

April 8, 2010 at 12:05 pm

#1148537

Thanks guys, I appreciate the help!

Jude