I recently needed to write a stored procedure that would take a search string from a web form and search three columns: name, description, and keywords. The keywords column and the input string can both be comma delimited. The key to the sproc is going to be the SQL IN operator. The IN operator can be used in a where clause to filter based on a list like so:
Where
id IN (1,4,6,10,13)
So what I needed was:
Where
id IN (@searchString)
But you can't do that so I needed to write a split function to split out @searchString. Note: I think there maybe be a split function in SQL Server 2005/2008 but I was using SQL Express.
Here is the final result:
ALTER FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
-------------------------------------------------------------------------------------
ALTER PROCEDURE SearchApplications
(
@searchWord varchar(100)
)
AS
SELECT ID, [Name], Description FROM Applications
WHERE
[Keywords] IN (Select Value from dbo.Split(@searchWord,',')) or
[Name] IN (Select Value from dbo.Split(@searchWord,',')) or
[Name] = @searchWord or
[Description] IN (Select Value from dbo.Split(@searchWord,',')) or
[Description] = @searchWord