November 18, 2009 at 5:18 pm
Hi,
I have a small problem and it is related to searching a column with one or more strings
I have a table as follow
-------------------------------------------------------------------------------
CREATE TABLE Movies1(
Mname VARCHAR(25),
Descript TEXT
)
-------------------------------------------------------------------------------
INSERT INTO Movies1 VALUES ('AAA','This movie is full of action')
INSERT INTO Movies1 VALUES ('BBB','This movie is full of drama')
INSERT INTO Movies1 VALUES ('CCC','Worst movie ever')
INSERT INTO Movies1 VALUES ('DDD','Sci-Fi fan will love this film')
INSERT INTO Movies1 VALUES ('EEE','Must watch')
INSERT INTO Movies1 VALUES ('FFF','Don't watch this film')
--------------------------------------------------------------------------------
Now if I need to search the descript column using LIKE operator, the following stored procedure will work. But the problem is it will only take 1 search string, and if I need to search using 2 or more search string then I will have to make similar stored procedure with more variables.
---------------------------------------------------------------------------------
USE [MovieDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SearchMovies]
@Query VARCHAR(50)
AS
SELECT Mname, Descript FROM Movies1 WHERE Descript LIKE '%'+@Query+'%'
----------------------------------------------------------------------------------
Since it is impossible to know how many search string will be there, so we can't make infinte number of stored procedure.
So can any one help me create a single stored procedure which can take 1 to maximum (say infinite) number of variables to get the result.
Since I am using this in ASP form to search the database, so it will be important to know how to pass the search strings to the given procedure too.
So please can any one help.
Thank you.
November 18, 2009 at 9:59 pm
Hi thanks for looking I solved my problem using FREETEXT.
Solution is given below
@Query VARCHAR(50)
AS
SELECT Mname
FROM Movies1
WHERE FREETEXT(descript,@Query)
Thanks again.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply