Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 26th, 2010, 07:10 AM
Registered User
 
Join Date: Aug 2008
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default sp returns no results, but query does.

Hi all,

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 does return a result, but removing a word and flipping the remaining words does not give results. The code is as follows:

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
The code refers to the tf_productSearch which is this:

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 > 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
These scripts return no results. However, typing the following code:

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

Many Thanks,

Paul





Similar Threads
Thread Thread Starter Forum Replies Last Post
SP Returns Nothing but runs fine...help [email protected] SQL Server 2000 1 May 15th, 2006 05:55 AM
Check if query returns results marcin2k Access VBA 10 December 9th, 2005 02:05 PM
access returns results but not when through ASP whyulil Classic ASP Databases 4 June 8th, 2004 11:18 PM
Results Page always returns '0 found' jonrayworth Dreamweaver (all versions) 4 November 9th, 2003 05:48 PM
SP with Results sidneyfuerte Pro VB 6 4 September 9th, 2003 01:48 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.