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.
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 INTSET @NecessaryMatches = 1IF @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 wordIF @Word1 IS NOT NULL INSERT INTO @Matches EXEC SearchWord @Word1-- Save the matches for the second wordIF @Word2 IS NOT NULL INSERT INTO @Matches EXEC SearchWord @Word2-- Save the matches for the third wordIF @Word3 IS NOT NULL INSERT INTO @Matches EXEC SearchWord @Word3-- Save the matches for the fourth wordIF @Word4 IS NOT NULL INSERT INTO @Matches EXEC SearchWord @Word4-- Save the matches for the fifth wordIF @Word5 IS NOT NULL INSERT INTO @Matches EXEC SearchWord @Word5-- Calculate the IDs of the matching productsDECLARE @Results TABLE(RowNumber INT, [KEY] INT NOT NULL, Rank INT NOT NULL)-- Obtain the matching products INSERT INTO @ResultsSELECT ROW_NUMBER() OVER (ORDER BY COUNT(M.Rank) DESC), M.[KEY], SUM(M.Rank) AS TotalRankFROM @Matches MGROUP BY M.[KEY]HAVING COUNT(M.Rank) >= @NecessaryMatches-- return the total number of results using an OUTPUT variableSELECT @HowManyResults = COUNT(*) FROM @Results-- populate the table variable with the complete list of productsSELECT 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 RON Product.ProductID = R.[KEY]WHERE R.RowNumber > (@PageNumber - 1) * @ProductsPerPage AND R.RowNumber <= @PageNumber * @ProductsPerPageORDER 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? |
|
|
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 |
|
|
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 6There 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 6Invalid object name 'Product'.(0 row(s) affected)Msg 208, Level 16, State 1, Procedure SearchCatalog, Line 74Invalid object name 'Product'."code for SearchWord:CREATE PROCEDURE SearchWord (@Word NVARCHAR(50))ASSET @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. |
|
|
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. |
|
|
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 YourDatabaseNameHereGOSELECT * FROM sys.procedures p WHERE NAME = 'SearchCatalog'; If you don't see it there, see if it is in the master database.USE MASTERGOSELECT * FROM sys.procedures p WHERE NAME = 'SearchCatalog'; |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-03 : 09:55:30
|
Run this querySELECT * 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. |
|
|
|
|
|
|
|