Click here to Skip to main content
15,312,540 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I am sending a string(param) to a function and that function returns me a table containing rows of words. Here I want to fetch the records from table which containing all those words. i tried the below query as a stored procedure but it returns me like 'OR' condition. Can anyone help me with this?
select DISTINCT (Field1+ ' ' +Field2) from table1 where exists(select DField from function1(@Param)where Field2 like '%'+DField+'%'


[edit]

Thanks for the reply. Here the stored procedure.

SQL
ALTER procedure [dbo].[ICDSEARCH]
@Description nvarchar(600)
as
begin
set nocount on;
 select DISTINCT (CODE+ ' ' +DESCRIPTION) from ICDCODE where exists(select DField from dbo.splitstring(@description)where DESCRIPTION like '%'+DField+'%')

END


Here the Table Valued Function
ALTER FUNCTION  [dbo].[splitstring]
(
   @Param_des varchar(500)
)
RETURNS @d_Table TABLE(DField nvarchar(200))

AS
BEGIN  
   IF (LEN(@Param_des) = 0) 
      RETURN

   DECLARE @SpacePos smallint
   SET @SpacePos = CHARINDEX(' ', RTRIM(LTRIM(@Param_des)))	  

   IF @SpacePos = 0
       INSERT INTO @d_Table 
              VALUES(CONVERT(nvarchar ,RTRIM(LTRIM(@Param_des))))
   ELSE 
       BEGIN
           WHILE LEN(@Param_des)> 1
	   BEGIN
	     SET @SpacePos = CHARINDEX(' ', RTRIM(LTRIM(@Param_des)))
             INSERT INTO @d_Table 
                      VALUES(CONVERT(nvarchar,SUBSTRING(RTRIM(LTRIM(@Param_des)),1, @SpacePos - 1)))
	     SET @Param_des = SUBSTRING(RTRIM(LTRIM(@Param_des)), @SpacePos + 1 , LEN(RTRIM(LTRIM(@Param_des))))
	     SET @SpacePos = CHARINDEX(' ', RTRIM(LTRIM(@Param_des)))
	     IF @SpacePos = 0
	     BEGIN
                 INSERT INTO @d_Table VALUES(CONVERT(nvarchar ,RTRIM(LTRIM(@Param_des))))
                 BREAK
	     END
	   END
       END
       RETURN 
end



the stored procedure above will give me a result where description containing any of the words of the table that i got from the function(since i have used like it acts as 'OR'). instead of that i want the output that gives me the records in which all the words occurs.(AND Operation instead of OR)

Hope you got it.
Posted
Updated 30-Jan-11 22:56pm
v3
Comments
Umair Feroze 28-Jan-11 6:47am
   
Please elaborate your question. What does your table contain and What this function returns?
Sunasara Imdadhusen 29-Jan-11 0:00am
   
Not clear!!

1 solution

Your question isn't clear but here's a guess:

If you can add full-text search capabilities to the database you can use the CONTAINS functionality:

SQL
WHERE ProductDescriptionID <> 5 AND
   CONTAINS(Description, ' Aluminum AND spindle');


http://msdn.microsoft.com/en-us/library/ms187787.aspx[^]
   
Comments
Estys 31-Jan-11 4:36am
   
OP says thanks, edited the question.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900