• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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.
0
MKItani
Asked:
MKItani
4 Solutions
 
EvilPostItCommented:
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
 
mimran18Commented:
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
 
Guy Hengel [angelIII / a3]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
 
AnuTijiCommented:
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now