Keyword "IN"+MS sql server 2005

i want to send to the stored procedure the name of students and because each time i want to get a set of names so i am trying to use the keyword "In" as follow

Stored Porcedure Name_Students
@Name varchar(MAX)
begin
select * from Student where Name in (@Name)
end

Calling the stored procedure "Name_Students"
Declare @Name Varchar(Max)
Set @Name ='Jean,Samir,Hiba,Rola,Nick'
Execute Name_Students @Name

But i get nothing as out put, What is the problem in my code?
and in case just i set one name as parameter i get an out put like this:
Declare @Name Varchar(Max)
Set @Name ='Jean'
Execute Name_Students @Name

but i need to send more than one name so how i can solve the problem.
Please Advice.
MKItaniAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you can already read this article to see how to work without dynamic sql:
http://www.experts-exchange.com/A_1536.html
0
 
EvilPostItConnect With a Mentor Commented:
This is because the select is effectivly search for 'Jean,Samir,Hiba,Rola,Nick' as a single string.

If you change you sp to

DECLARE @tsql VARCHAR(2000)
SET @tsql='SELECT * FROM STUDENT WHERE Name in ('+@NAME+')'
EXEC @tsql

Open in new window


And change the @name input to

Declare @Name Varchar(Max)
Set @Name ='''Jean'',''Samir'',''Hiba'',''Rola'',''Nick'''
Execute Name_Students @Name

Open in new window


This should do the trick.
0
 
mimran18Connect With a Mentor Commented:
Hi
   try this.

Drop table test
Go
Create table test
(ID int,
[SName] nvarchar(50))
Go
Insert  into test values (1,'John')
Insert  into test values (2,'Robert')
Insert  into test values (3,'William')
GO

Drop Procedure Name_Students
GO
Create Procedure Name_Students
@Name varchar(MAX)
as
begin
Declare @SQl as nvarchar(Max)
Set @SQl=''
SET @Name=REPLACE(@Name,',',''',''')    
Print @Name
Set @SQl='Select * from test Where  [SName]  In (''' + @Name + ''')'
Print @SQl
Exec (@SQL)

end
Go
Name_Students 'John,Robert'
Go
Name_Students 'John'
Go

Open in new window

0
 
AnuTijiConnect With a Mentor Commented:
Hi

You can either use dynamic sql or use a table valued function to split the comma seperated list into a table.
CREATE FUNCTION [dbo].[ufn_CSVToTable] ( @StringInput VARCHAR(8000) )
RETURNS @OutputTable TABLE ( [String] VARCHAR(10) )
AS
BEGIN

    DECLARE @String    VARCHAR(10)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(',', @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(',', @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END
    
    RETURN
END
GO

Open in new window

0
 
mimran18Commented:
Agree with AngelIII, we can also do it with the help of xml without dynamic sql.
Here is the link for your reference.

http://raresql.com/2011/12/21/how-to-use-multiple-values-for-in-clause-using-same-parameter-sql-server/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.