problem with "IN" Operator in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hi,
my sql command is like this:

SQL

DECLARE @str nvarchar='1,2,3,4,5,6,7'
SELECT * FROM mytable
WHERE ID IN (@str)

but the result is the first row(id=1)
how can retrieve all row (1,2,3,4,5,6,7)
i use sql server 2008

Posted 13-Apr-13 4:37am

jiji2663

Updated 13-Apr-13 4:42am

v2

Add a Solution

Comments

gvprabu 14-Apr-13 13:48pm

Hi...
In Variable Declaration place, U didn't gave the nvarchar Size... one thing how u got one row(id=1). With out dynamic Query or other methods u can't get the result.

3 solutions

Solution 3

Hi...

Try Dynamic Query as follows.

SQL

DECLARE @str varchar(100)='1,2,3,4,5,6,7', @SqlStr VARCHAR(1000)

CREATE TABLE mytable (ID INT)
INSERT INTO mytable(ID) VALUES(1),(2),(3),(4), (5), (6), (7)

SELECT @Sqlstr='SELECT * FROM mytable
WHERE ID IN ('+@str+')'

PRINT @SqlStr
EXEC(@SqlStr)

DROP TABLE mytable

Regards,
GVPrabu

Posted 14-Apr-13 7:46am

gvprabu

Solution 2

SQL

CREATE FUNCTION [dbo].[SplitDelimited]
(	
	@List nvarchar(MAX),
	@SplitOn nvarchar(20)
)
RETURNS @RtnValue table (
	Value nvarchar(100)
)
AS
BEGIN
	While (Charindex(@SplitOn,@List)>0)
	Begin 
		Insert Into @RtnValue (value)
		Select 
			Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) 
		Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
	End 
	
	Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END

DECLARE @str nvarchar(max)='1,2,3,4,5,6,7'

SELECT * FROM myTable WHERE id IN (SELECT Value FROM SplitDelimited(@str))

Posted 13-Apr-13 5:50am

Member 8488336

Updated 13-Apr-13 5:56am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-04-13T05:38:00

Unfortunately, you can't do it that simply - it just doesn't work!
You have to go a bit round the houses and convert the CSV to a list that SQL underatsnds - in practice a table of some form. Here's how I do it:

SQL

DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = '2,3,177,'
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ','
CREATE TABLE #tempTab (id int not null)
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO #tempTab (id) VALUES (@VALUE)
END
SELECT * FROM myTable WHERE id IN (SELECT id FROM #tempTab)
DROP TABLE #tempTab

It's not nice, but it works! :laugh: