存储过程不返回结果，但查询返回结果

Question

我刚刚设法启用全文搜索，以便更轻松地在系统中搜索产品。但是，我使用的是其他人构建的 sp，它没有返回“值班呼叫”的任何结果。事实上，我正在搜索“使命召唤”，系统中的一个产品。如果我输入“使命召唤”，它确实会返回结果，但删除单词并翻转其余单词不会给出结果。代码如下：

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

该代码引用了 tf_productSearch，如下所示：

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 > 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 >= @Start) AND (TmpID <= @END)        

    RETURN


END

在经典 ASP 网页内进行此调用，代码如下：

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 " _
          & cData.ProcFld("MODE","S1",2,True) _
          & cData.ProcFld("PG",PG,0,True) _
          & cData.ProcFld("ROWCT",C_PRODUCTS_FE_PRODUCTROWCOUNT,0,True) _
          & cData.ProcFld("COLCT",C_PRODUCTS_FE_PRODUCTCOLCOUNT,0,True) _
          & cData.ProcFld("SearchStr",SearchStr,2,True) _
          & cData.ProcFld("StoreItemCatID",StoreItemCatID,0,False)
paryProducts = cData.RSArray()

然而，这些脚本不返回任何结果。但是，直接在 sql-server 的查询窗口中键入以下代码：

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

确实返回结果。我不适合这里，想知道你们中的一位经验丰富的程序员是否会在这里看到一些明显的错误。如果你们能提供任何帮助，我们将不胜感激。

非常感谢，

保罗

Answer 1

好吧，这不是重写，因为我没有时间也没有要求来满足您的需求，但这里有一些提示...

1. 如果您不需要表值函数，请删除它；将该逻辑移至存储过程中。

2. 摆脱 IF @StoreCatItem IS NULL 语句，并在 WHERE 中使用类似 WHERE (@StoreCatItem IS NULL OR a.StoreItemCatID = @StoreCatItem) 的结构子句合并为 1 个 SELECT 语句。如果您的目的是鼓励适当的索引使用，请将 OPTION(RECOMPILE) 提示添加到查询末尾，而不是重复 sql。

3. 我发现您正在存储过程中进行分页。临时表是一个好主意，但更改其定义和插入查询以仅包含以正确方式排序的完整结果集的主键值以及 IDENTITY 列，但您很可能只需要一个 INT 列，而不是 BIGINT，这在临时表上并不是很重要。插入表变量的第一个查询是使用 WHERE 条件进行筛选的位置。对于您的每个标准，都可以像这样使用上面提到的构造。
   WHERE（@StoreCatItem 为 NULL 或
a.StoreItemCatID = @StoreCatItem 或
a.StoreItemCatID IN (
从 StoreItemCat x 中选择 x.StoreItemCatID，其中 x.ParentID = @StoreItemCatID))
和自由文本（a.ProductName，@SearchStr）
AND a.StoreItemCatID = @StoreItemCatID
AND a.已删除 = 0
AND a.Active = 1

4. 返回记录的最终 select 语句应该在表变量中的键和真实数据之间再次执行简单的 INNER JOIN ；您可以在此处选择所需的所有实际字段，并且无需再次重复您的条件，因为联接将过滤您的结果。

5. 如果您只是在学习，那么迭代创建是无可替代的。从您知道有效的方法开始，并以此为基础进行构建。

享受！

Answer 2

tf_ProductSearch 函数中 WHERE 子句的元素在 ELSE 部分中重复/不正确地关联逻辑（它与您的工作查询不匹配）。这可能就是问题所在。