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 ]