SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with Stored Procedurw
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hozdaman
Starting Member

USA
5 Posts

Posted - 10/02/2012 :  20:56:25  Show Profile  Reply with Quote
Hi,
First I would like to say I am so glad I found this forum. I am very new to SQL. Currently I am building an ecommerce store with help of a few books.

I am at the point where I am create a search option for the store. I created a store procedure for the SearchCatalog and one call SearchWord.

For some reason I keep having issues with the call to the Product table. It exists in the database.


Please help. Thanks.

CREATE PROCEDURE SearchCatalog 
(@DescriptionLength INT,
 @PageNumber TINYINT,
 @ProductsPerPage TINYINT,
 @HowManyResults INT OUTPUT,
 @AllWords BIT,
 @Word1 NVARCHAR(15) = NULL,
 @Word2 NVARCHAR(15) = NULL,
 @Word3 NVARCHAR(15) = NULL,
 @Word4 NVARCHAR(15) = NULL,
 @Word5 NVARCHAR(15) = NULL)
AS

/* @NecessaryMatches needs to be 1 for any-word searches and
   the number of words for all-words searches */
DECLARE @NecessaryMatches INT
SET @NecessaryMatches = 1
IF @AllWords = 1 
  SET @NecessaryMatches =
    CASE WHEN @Word1 IS NULL THEN 0 ELSE 1 END + 
    CASE WHEN @Word2 IS NULL THEN 0 ELSE 1 END + 
    CASE WHEN @Word3 IS NULL THEN 0 ELSE 1 END +
    CASE WHEN @Word4 IS NULL THEN 0 ELSE 1 END +
    CASE WHEN @Word5 IS NULL THEN 0 ELSE 1 END;

/* Create the table variable that will contain the search results */
DECLARE @Matches TABLE
([Key] INT NOT NULL,
 Rank INT NOT NULL)

-- Save matches for the first word
IF @Word1 IS NOT NULL
  INSERT INTO @Matches
  EXEC SearchWord @Word1

-- Save the matches for the second word
IF @Word2 IS NOT NULL
  INSERT INTO @Matches
  EXEC SearchWord @Word2

-- Save the matches for the third word
IF @Word3 IS NOT NULL
  INSERT INTO @Matches
  EXEC SearchWord @Word3

-- Save the matches for the fourth word
IF @Word4 IS NOT NULL
  INSERT INTO @Matches
  EXEC SearchWord @Word4

-- Save the matches for the fifth word
IF @Word5 IS NOT NULL
  INSERT INTO @Matches
  EXEC SearchWord @Word5

-- Calculate the IDs of the matching products
DECLARE @Results TABLE
(RowNumber INT,
 [KEY] INT NOT NULL,
 Rank INT NOT NULL)

-- Obtain the matching products 
INSERT INTO @Results
SELECT ROW_NUMBER() OVER (ORDER BY COUNT(M.Rank) DESC),
       M.[KEY], SUM(M.Rank) AS TotalRank
FROM @Matches M
GROUP BY M.[KEY]
HAVING COUNT(M.Rank) >= @NecessaryMatches

-- return the total number of results using an OUTPUT variable
SELECT @HowManyResults = COUNT(*) FROM @Results

-- populate the table variable with the complete list of products
SELECT Product.ProductID, Name,
       CASE WHEN LEN(Description) <= @DescriptionLength THEN Description 
            ELSE SUBSTRING(Description, 1, @DescriptionLength) + '...' END 
       AS Description, Price, Thumbnail, Image, PromoFront, PromoDept 
FROM Product 
INNER JOIN @Results R
ON Product.ProductID = R.[KEY]
WHERE R.RowNumber > (@PageNumber - 1) * @ProductsPerPage
  AND R.RowNumber <= @PageNumber * @ProductsPerPage
ORDER BY R.Rank DESC

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 10/02/2012 :  21:27:27  Show Profile  Reply with Quote
Great, but what's the actual problem/error? What is it doing/not doing?
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 10/03/2012 :  01:33:41  Show Profile  Visit jackv's Homepage  Reply with Quote
If you run the code in SSMS , what error do you see?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

hozdaman
Starting Member

USA
5 Posts

Posted - 10/03/2012 :  05:32:49  Show Profile  Reply with Quote
When I run the stored proc I get the following message:

"Msg 2714, Level 16, State 3, Procedure SearchWord, Line 6
There is already an object named 'SearchWord' in the database."

Thinking my stored proc works I try the following to verify it:

EXEC SearchCatalog @DescriptionLength=20,@PageNumber =1, @ProductsPerPage=10, @HowManyResults=null, @AllWords=0,@Word1 = 'balloon'


When I execute this I get the following errors:

"Msg 208, Level 16, State 1, Procedure SearchWord, Line 6
Invalid object name 'Product'.

(0 row(s) affected)
Msg 208, Level 16, State 1, Procedure SearchCatalog, Line 74
Invalid object name 'Product'."

code for SearchWord:

CREATE PROCEDURE SearchWord (@Word NVARCHAR(50))
AS

SET @Word = 'FORMSOF(INFLECTIONAL, "' + @Word + '")'

SELECT COALESCE(NameResults.[KEY], DescriptionResults.[KEY]) AS [KEY],
       ISNULL(NameResults.Rank, 0) * 3 + 
       ISNULL(DescriptionResults.Rank, 0) AS Rank 
FROM 
  CONTAINSTABLE(Product, Name, @Word, 
                LANGUAGE 'English') AS NameResults
  FULL OUTER JOIN
  CONTAINSTABLE(Product, Description, @Word, 
                LANGUAGE 'English') AS DescriptionResults
  ON NameResults.[KEY] = DescriptionResults.[KEY]


I think it has something to do with my Product Table not being recognized. I could be way off base here.

Thanks.
Go to Top of Page

hozdaman
Starting Member

USA
5 Posts

Posted - 10/03/2012 :  05:39:30  Show Profile  Reply with Quote
One other thing I notice when I go to visual studio I don't see a SearchWord or a SearchCatalog in the Stored Procedure folder in Server Explorer.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  07:00:35  Show Profile  Reply with Quote
See if you really have the stored proc in the database - by running this query:
USE YourDatabaseNameHere
GO
SELECT * FROM sys.procedures p WHERE NAME = 'SearchCatalog';
If you don't see it there, see if it is in the master database.
USE MASTER
GO
SELECT * FROM sys.procedures p WHERE NAME = 'SearchCatalog';
Go to Top of Page

hozdaman
Starting Member

USA
5 Posts

Posted - 10/03/2012 :  07:49:11  Show Profile  Reply with Quote
I ran both queries and both of the Stored Procedures are in the database.

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  07:56:33  Show Profile  Reply with Quote
Is the Product table full-text indexed?

Also, is the Product table in the same database as the stored procedure and is it in the dbo schema ? If not qualify with the schema name and database name.
Go to Top of Page

hozdaman
Starting Member

USA
5 Posts

Posted - 10/03/2012 :  09:49:33  Show Profile  Reply with Quote
The product table is full-indexed.

"
Also, is the Product table in the same database as the stored procedure and is it in the dbo schema ? If not qualify with the schema name and database name."

I'm not sure how to check or do this.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/03/2012 :  09:55:30  Show Profile  Reply with Quote
Run this query
SELECT * FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME='Product'
Then, concatenate the Table_catalog value, table_schema value and table_name value with periods in between. For example, MyDatabaseName.dbo.Product. Then, use that instead of Product when referring to table name Product.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000