Passing multiple parameter with IN operator using MS SQL Server 2005

How to pass multiple Parameter with IN Operator using MS SQL Server 2005.


Procedure ....

------------------

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

--**************************************************************************

-- ***** Description      : Searching data

-- ***** Create By        : Narendra Chejara

-- ***** Created Date     : 01/12/2010

-- ***** Modify History

-- *************************************************************************

 

 

ALTER PROCEDURE [dbo].[SearchingData]

(

      @Keyword NVARCHAR(100),

      @ReturnValue INT OUTPUT

)

AS

BEGIN

  SELECT DISTINCT SkillMiddleDetails.AppID,ApplicationDetails.Name  + ' ' + ApplicationDetails.Lastname as Name,ApplicationDetails.ContactNumber, ApplicationDetails.FilePath,

ApplicationDetails.OFilename,SUBSTRING(ApplicationDetails.Skills,0,13) + ' ...' as Skills FROM   ApplicationDetails INNER JOIN SkillMiddleDetails ON     ApplicationDetails.AppID = SkillMiddleDetails.AppID INNER JOIN SkillDetails ON     SkillMiddleDetails.SkillID = SkillDetails.SkillID WHERE (SkillDetails.SkillName in (select Data from SplitString(@Keyword,',')))        

                  SET @ReturnValue = @@identity

            END

     

     

END

 

Here i create a sql function which return multiple string with Comma (,) separator

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[SplitString]

(

      @SplitStr nvarchar(1000),

      @SplitChar nvarchar(5)

)

RETURNS @RtnValue table

(

      Data nvarchar(50)

)

AS

BEGIN

      Declare @Count int

      Set @Count = 1

 

      While (Charindex(@SplitChar,@SplitStr)>0)

            Begin

                  Insert Into @RtnValue (Data)

                  Select

                  Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))

 

                  Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))

                  Set @Count = @Count + 1

            End

 

      Insert Into @RtnValue (Data)

      Select Data = ltrim(rtrim(@SplitStr))

 

      Return

END



[Note : I made this procedure according to my requirement you use your own Select Query and pass this SplitString function  ]