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.
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.