SELECT *
FROM Directory.Employees
WHERE CONTAINS ( *, '"*ron*"' )
INTERSECT
SELECT *
FROM Directory.Employees
WHERE CONTAINS ( *, '"*over*"' )

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SearchEmployees( SqlString searchText )
    {
        /* This sproc builds a sql statement from the searchText parameter.
         * The words in the searchtext delimited by space are split into an array.
         * The array is traversed and a tsql statement is constructed.
         * 
         * Example:  @searchText = 'ron over dwayne'
         * 
         * HERE IS THE CONSTRUCTED SQL STATEMENT:         * 
         * 
                    SELECT *
                    FROM Directory.Employees
                    WHERE CONTAINS ( *, '"*ron*"' )
                    INTERSECT
                    SELECT *
                    FROM Directory.Employees
                    WHERE CONTAINS ( *, '"*over*"' )
                    INTERSECT
                    SELECT *
                    FROM Directory.Employees
                    WHERE CONTAINS ( *, '"*dwayne*"' )
         * 
         * The result set would be all rows that match EVERY WORD in the array in AT LEAST ONE COLUMN.
         * Wildcards are appended before and after each word.
        */

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = new SqlConnection("context connection=true");

        // template for sql statement; {0} is where each word will be inserted
        string sqlTemplate = "SELECT * FROM Directory.Employees WHERE CONTAINS ( *, '\"*{0}*\"' )";
        string sql = string.Empty;

        // split parameter into array of words
        string[] searchWords = searchText.ToString().Split(' ');

        // iterate through array, building sql statement
        for (int i = 0; i < searchWords.Length; i++)
        {
            sql += string.Format(sqlTemplate, searchWords[i]);
            if (i != searchWords.Length - 1)
            {
                sql += " INTERSECT ";           // on all but last pass, INTERSECT is inserted into statement
            }
        }

        // set commandtext of command to sql statement we just built
        cmd.CommandText = sql;

        //Get a reference to the SqlPipe for this calling context
        SqlPipe pipe = SqlContext.Pipe;

        // do the deed
        cmd.Connection.Open();
        pipe.Send(cmd.ExecuteReader());
        cmd.Connection.Close();

    }
};

CREATE FUNCTION dbo.GetList(@NameList nvarchar(MAX))
RETURNS @RetTable Table (name nvarchar(20))
AS
BEGIN
    DECLARE @FirstPos int, @EndPos int, @Len int
    SELECT @Len = Len(@NameList), @FirstPos = 0

    WHILE @FirstPos < @Len
    BEGIN
        SELECT @EndPos = charindex(' ', @NameList, @FirstPos + 1)

        IF @EndPos = 0 SELECT @EndPos = @Len + 1

        INSERT INTO @RetTable  VALUES (LTrim(SUBSTRING(@IDList, @FirstPos + 1, @EndPos - @FirstPos - 1)))

        SELECT @FirstPos = @EndPos
    END

    RETURN
END

GO

CREATE PROCEDURE dbo.Search (@NameList nvarchar(MAX),@Column varchar(100))
AS
BEGIN
   IF @Column = ''
   BEGIN
      SELECT <COLUMNList>
      FROM Directory.Employee e,
           dbo.GetList(@NameList) l
      WHERE (e.FirstName like '%'+l.name+'%' OR
            e.LastName like '%'+l.name+'%' OR
           -- the rest of the columns come here
             )
   END

   IF @Column = 'FirstName'
   BEGIN
      SELECT <COLUMNList>
      FROM Directory.Employee e,
           dbo.GetList(@NameList) l
      WHERE (e.FirstName like '%'+l.name+'%')
   END
-- The rest of the code comes here. 



END
GO

quote:

---

Originally posted by ronnieoverby

Blindman, will you just stop looking at this thread?

---