title: stored proc returns no results, but query does
body: <p>I have just managed to enable full-text searching to make searching for products in my system easier. However, I am using an sp that was built by someone else and it is not returning any results for "Duty Call". In actual fact I am searching for "Call of Duty", a product in the system. If I type "Call of Duty" it <strong>does</strong> return a result, but removing a word and flipping the remaining words does not give results. The code is as follows:</p>
  
  <pre><code>USE [storeboard]
  GO
  /****** Object:  StoredProcedure [sbuser].[sp_ProductSearch]    Script Date: 08/26/2010 05:57:20 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROC [sbuser].[sp_ProductSearch]
      @StoreItemID bigint = null,
      @StoreMemberID bigint = null,
      @ProductName varchar(50) = null,
      @ProductDesc varchar(1000) = null,
      @ItemPrice float = null,
      @Active bit = null,
      @Deleted bit = null,
      @CreateDate datetime = null,
      @ShipWeight float = null,
      @TaxExempt bit = null,
      @ShipCost float = null,
      @Keywords varchar(1000) = null,
      @PG int = 1,
      @ROWCT numeric(18,2) = 1,
      @COLCT numeric(18,2) = 1,
      @MODE varchar(50),
      @StoreItemCatID bigint = null,
      @SearchStr varchar(100) = null
  
  AS
  
  
  IF @MODE = 'S1'    
      BEGIN
          SELECT
          StoreItemID,
          ProductName,
          ItemPrice,
          PG,
          MAXPG,
          TOTALRECS,
          CoverImg,
          StoreItemCatID,
          Active
          FROM sbuser.tf_ProductSearch(@PG,@ROWCT,@COLCT,@StoreItemCatID,@SearchStr)
      END
  </code></pre>
  
  <p>The code refers to the tf_productSearch which is this:</p>
  
  <pre><code>USE [storeboard]
  GO
  /****** Object:  UserDefinedFunction [sbuser].[tf_ProductSearch]    Script Date: 08/26/2010 05:46:36 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER FUNCTION [sbuser].[tf_ProductSearch] (
      @PG int,
      @ROWCT numeric(18,2),
      @COLCT numeric(18,2),
      @StoreItemCatID bigint,
      @SearchStr varchar(100) = null)
  
  RETURNS @OUT TABLE (
      StoreItemID bigint,
      ProductName varchar(50),
      ProductDesc varchar(1000),
      ItemPrice float,
      Active bit,
      CreateDate datetime,
      ShipWeight float,
      TaxExempt bit,
      ShipCost float,
      Keywords varchar(1000),
      PG int,
      MAXPG INT,
      TOTALRECS INT,
      CoverImg varchar(50),
      StoreItemCatID bigint )
  
  AS
  
  BEGIN
  
          DECLARE @START numeric(18,2);
          DECLARE @END numeric(18,2);
          DECLARE @SIZE numeric(18,2);
          DECLARE @MAXPG numeric(18,2);
          DECLARE @TOTALRECS numeric(18,2);
          DECLARE @TOTALRECS_INT int;
          DECLARE @MAXPG_INT int;
          DECLARE @TOTALRECS_REMAINDER numeric(18,2);
          SET @SIZE = @ROWCT * @COLCT
          SET @Start = (((@PG - 1) * @Size) + 1)
          SET @END = (@START + @SIZE - 1)
  
  
          DECLARE @TMP1 TABLE (
          TmpID bigint identity(1,1) primary key,
          StoreItemID bigint,
          ProductName varchar(50),
          ProductDesc varchar(1000),
          ItemPrice float,
          Active bit,
          CreateDate datetime,
          ShipWeight float,
          TaxExempt bit,
          ShipCost float,
          Keywords varchar(1000),
          CoverImg varchar(50),
          StoreItemCatID bigint )
  
  
          IF @StoreItemCatID IS NULL
              BEGIN
                  INSERT INTO @TMP1 
                  SELECT
                  a.StoreItemID,
                  a.ProductName,
                  a.ProductDesc,
                  a.ItemPrice,
                  a.Active,
                  a.CreateDate,
                  a.ShipWeight,
                  a.TaxExempt,
                  a.ShipCost,
                  a.Keywords,
                  sbuser.sf_StoreItemCoverImg(a.StoreItemID) AS CoverImg,
                  a.StoreItemCatID
                  FROM sbuser.StoreItem a
                  WHERE FREETEXT (a.ProductName, @SearchStr)
                  AND Deleted = 0    
                  AND Active = 1
                  ORDER BY a.ProductName 
              END
          ELSE
  
              BEGIN
                  INSERT INTO @TMP1 
                  SELECT
                  a.StoreItemID,
                  a.ProductName,
                  a.ProductDesc,
                  a.ItemPrice,
                  a.Active,
                  a.CreateDate,
                  a.ShipWeight,
                  a.TaxExempt,
                  a.ShipCost,
                  a.Keywords,
                  sbuser.sf_StoreItemCoverImg(a.StoreItemID) AS CoverImg,
                  a.StoreItemCatID
                  FROM sbuser.StoreItem a
                  WHERE FREETEXT (a.ProductName, @SearchStr)
                  AND a.StoreItemCatID = @StoreItemCatID
                  AND a.Deleted = 0    
                  AND a.Active = 1
                  OR a.StoreItemCatID IN (SELECT StoreItemCatID FROM StoreItemCat WHERE ParentID = @StoreItemCatID)
                  AND FREETEXT (a.ProductName, @SearchStr)
                  AND a.Deleted = 0    
                  AND a.Active = 1
                  ORDER BY a.ProductName
              END
  
  
  
          SELECT @TOTALRECS = MAX(TMPID) FROM @TMP1
          SELECT @MAXPG = @TOTALRECS / @SIZE
          SET @TOTALRECS_REMAINDER = @TOTALRECS % @SIZE
  
          SET @MAXPG_INT = CAST(@MAXPG AS INT)
          SET @TOTALRECS_INT = CAST(@TOTALRECS AS INT)
  
  
          IF @TOTALRECS_REMAINDER &gt; 0 
              BEGIN
                  SET @MAXPG_INT = @MAXPG_INT + 1
              END        
  
  
  
          INSERT INTO @OUT
          SELECT 
              StoreItemID,
              ProductName,
              ProductDesc,
              ItemPrice,
              Active,
              CreateDate,
              ShipWeight,
              TaxExempt,
              ShipCost,
              Keywords,
              @PG,
              @MAXPG_INT,
              @TOTALRECS_INT,
              CoverImg,
              StoreItemCatID
              FROM @TMP1
              WHERE (TmpID &gt;= @Start) AND (TmpID &lt;= @END)        
  
      RETURN
  
  
  END
  </code></pre>
  
  <p>This call inside a classic ASP web page with the following code:</p>
  
  <pre><code>Dim ProductCat
  Dim paryProducts
  Dim ProdMaxPG
  Dim pstrProductList
  
  Const C_PRODUCTS_FE_PRODUCTROWCOUNT = 4
  Const C_PRODUCTS_FE_PRODUCTCOLCOUNT = 5
  SearchStr = "duty call"
  StoreItemCatID = ""
  
  cData.SQL = "sp_ProductSearch " _
            &amp; cData.ProcFld("MODE","S1",2,True) _
            &amp; cData.ProcFld("PG",PG,0,True) _
            &amp; cData.ProcFld("ROWCT",C_PRODUCTS_FE_PRODUCTROWCOUNT,0,True) _
            &amp; cData.ProcFld("COLCT",C_PRODUCTS_FE_PRODUCTCOLCOUNT,0,True) _
            &amp; cData.ProcFld("SearchStr",SearchStr,2,True) _
            &amp; cData.ProcFld("StoreItemCatID",StoreItemCatID,0,False)
  paryProducts = cData.RSArray()
  </code></pre>
  
  <p>These scripts however return no results. However, typing the following code directly into the query window of the sql-server:</p>
  
  <pre><code>USE storeboard
  GO
  DECLARE @SearchStr varchar(50)
  SET @SearchStr = 'duty call';
  SELECT
                  a.StoreItemID,
                  a.ProductName,
                  a.ProductDesc,
                  a.ItemPrice,
                  a.Active,
                  a.CreateDate,
                  a.ShipWeight,
                  a.TaxExempt,
                  a.ShipCost,
                  a.Keywords,
                  a.StoreItemCatID
                  FROM sbuser.StoreItem a
                  WHERE FREETEXT (a.ProductName, @SearchStr)
                  AND a.Deleted = 0    
                  AND a.Active = 1
                  ORDER BY a.ProductName
  </code></pre>
  
  <p><strong>does</strong> return a result. I am out of my league here and wondered if one of you experienced programmers may see something obviously wrong here. Any help you guys or gals can provide would be greatly appreciated.</p>
  
  <p>Many Thanks,</p>
  
  <p>Paul</p>