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 ]