Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with Stored Procedurw

Author  Topic 

hozdaman
Starting Member

5 Posts

Posted - 2012-10-02 : 20:56:25
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 - 2012-10-02 : 21:27:27
Great, but what's the actual problem/error? What is it doing/not doing?
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-03 : 01:33:41
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

5 Posts

Posted - 2012-10-03 : 05:32:49
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

5 Posts

Posted - 2012-10-03 : 05:39:30
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 07:00:35
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

5 Posts

Posted - 2012-10-03 : 07:49:11
I ran both queries and both of the Stored Procedures are in the database.

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 07:56:33
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

5 Posts

Posted - 2012-10-03 : 09:49:33
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 09:55:30
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
   

- Advertisement -