Solved

Keyword "IN"+MS sql server 2005

Posted on 2011-09-06
5
215 Views
Last Modified: 2012-05-12
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
Comment
Question by:MKItani
5 Comments
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 100 total points
ID: 36487479
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
 
LVL 9

Assisted Solution

by:mimran18
mimran18 earned 100 total points
ID: 36487533
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 150 total points
ID: 36487534
you can already read this article to see how to work without dynamic sql:
http://www.experts-exchange.com/A_1536.html
0
 
LVL 4

Assisted Solution

by:AnuTiji
AnuTiji earned 150 total points
ID: 36487609
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
 
LVL 9

Expert Comment

by:mimran18
ID: 37322609
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now