title: fast search specific column for specific keywords with stored procedure
body: <p>I wrote a stored procedure that gets: </p>
  
  <ul>
  <li>"SearchKeys" - keys to search separated by ',' "key1,key2"</li>
  <li>"ToSearch" - Tables to search in separated by ',' with colums after ':' separated by '.' "table1:column1.column2,table2:column1.column2"</li>
  </ul>
  
  <p>At the end procedure returns table with name of table and row id were the key was found.</p>
  
  <p>here is the code:</p>
  
  <pre><code>--Search keys in tables
  CREATE PROCEDURE [dbo].[Search_All]
  (
      @SearchKeys nvarchar(50), --Keys to search separated by ','
      @ToSearch varchar(200) --Tables to search in separated by ',' with colums after ':' separated by '.'
  )
  AS
  BEGIN
      --create table with found values
      CREATE TABLE #Results (TargetId int, DBName varchar(20))
  
      --Split SearchKeys to Keys
      WHILE LEN(@SearchKeys) &gt; 0
       BEGIN
          DECLARE @Key NVARCHAR(25)
          IF CHARINDEX(',',@SearchKeys) &gt; 0
              SET  @Key = SUBSTRING(@SearchKeys,0,CHARINDEX(',',@SearchKeys))
          ELSE
              BEGIN
              SET  @Key = @SearchKeys
              SET @SearchKeys = ''
          END
          --Split ToSearch to Tables
          WHILE LEN(@ToSearch) &gt; 0
           BEGIN
              DECLARE @TableAndColums VARCHAR(200)
              IF CHARINDEX(',',@ToSearch) &gt; 0
                  SET  @TableAndColums = SUBSTRING(@ToSearch,0,CHARINDEX(',',@ToSearch))
              ELSE
                  BEGIN
                  SET  @TableAndColums = @ToSearch
                  SET @ToSearch = ''
                  END
              SET @ToSearch = REPLACE(@ToSearch,@TableAndColums + ',' , '')
              --Split @TableAndColums to Table and Colums
              --Select Table
              DECLARE @Table VARCHAR(25)
              SET  @Table = SUBSTRING(@TableAndColums,0,CHARINDEX(':',@TableAndColums))
              SET @TableAndColums = REPLACE(@TableAndColums,@Table + ':' , '')
              --Split to Colums
              WHILE LEN(@TableAndColums) &gt; 0
               BEGIN
                  DECLARE @Column VARCHAR(25)
                  IF CHARINDEX('.',@TableAndColums) &gt; 0
                      SET  @Column = SUBSTRING(@TableAndColums,0,CHARINDEX('.',@TableAndColums))
                  ELSE
                      BEGIN
                      SET  @Column = @TableAndColums
                      SET @TableAndColums = ''
                      END
  
                      BEGIN
                  --insert result in to #Results table
                  INSERT INTO #Results
                  EXEC
                      (
                          'SELECT ' + @Table + '.Id AS ''TargetId'', '''+@Table+''' AS ''DBName''
                          FROM ' + @Table +
                          ' WHERE  ' + @Column + ' LIKE N''%' + @Key + '%'''
                      )
                  END
  
               SET @TableAndColums = REPLACE(@TableAndColums,@Column + '.' , '')
              END
           END
       SET @SearchKeys = REPLACE(@SearchKeys,@Key + ',' , '')
      END
      --return found values
      SELECT DISTINCT TargetId , DBname FROM #Results
  END
  </code></pre>
  
  <p>For some reason it searches only for the first key ignoring all the rest keys. I can not find out why this is happening. Please help!</p>