SQL Server Performance Forum – Threads Archive

Modify SP to perform search with paged results

Hi all! I got some wonderful help in here the other day to get some paged query results out of my sp. Now I’d like to use the same idea and modify the sp to use on my search page to return paged results from whatever criteria the uses enters into a search box. However, I’m getting an error around the search phrase everytime. I would appreciate you taking a moment and letting me know what I’m doing wrong. Thanks! ********************************************************************
(
@Page int,
@RecsPerPage int,
@Search nvarchar(75)
)
AS SET NOCOUNT ON DECLARE
@FirstRec int,
@LastRec int,
@row_count int,
@error int — calculating start and end row
SELECT @FirstRec = (((@Page – 1) * @RecsPerPage) + 1)
SELECT @LastRec = @FirstRec + @RecsPerPage – 1 –Create a temporary table
CREATE TABLE #TempItems
(
seq_id int IDENTITY primary key,
ProductID int — I assume this is the PK of the table.
) — Insert the rows from tblItems into the temp.
INSERT INTO #TempItems (ProductID)
EXEC(‘SELECT ProductID FROM Products WHERE
ModelNumber LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
ModelName LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
Description LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
ThemeDesigner Like %’ + @Search + ‘% AND Disable IS NULL
OR
ProductID LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
Keywords LIKE %’ + @Search + ‘% AND Disable IS NULL
ORDER BY ModelName ‘ ) SELECT @row_count = @@ROWCOUNT, @error = @@ERROR
— @row_count now holds the number of rows inserted (meaning number or rows corrisponding to the conditions above)
— add error control here. SELECT theme, t.ProductID, ModelName, ThemeHomePage, Description, UnitCost, UnitSaleCost, ProductImage, ModelNumber,ThemeDesigner, Yourid, PMid,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems
WHERE seq_id > @LastRec
) FROM #TempItems t WITH (NOLOCK)
INNER JOIN Products p
ON p.ProductID = t.ProductID
WHERE seq_id BETWEEN @FirstRec AND @LastRec — calculating number or rows and pages
IF @@ROWCOUNT > 0
SELECT @row_count as ROW_COUNT,
@row_count/@RecsPerPage + case (@row_count % @RecsPerPage) when 0 then 0 ELSE 1 end as PageCount
ELSE
SELECT 0 ROW_COUNT, 0 PageCount — cleanup
DROP TABLE #TempItems SET NOCOUNT OFF

See this… http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=595
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=708 In the mean time let me look at the query.
HTH.
Gaurav Gaurav

I cannot understand your dymanic query. You are comparing different columns to the same value, and I think you don’t really mean to return all rows WHERE productID = %25% — this will return error converting to int
OR Description = %25%
etc… Besides, this query will result an error when datatype are different and conversion between datatypes need to be explicit. But maybe I understand what you are trying to achieve. And considering that you are aware that the current query will ALWAYS perform a table scan, you can do this: CREATE PROCEDURE my_sproc
(
, @ProductID int = NULL
, @ModelName varchar(50) = NULL
, @Description varchar(50) = NULL
, @ModelNumber varchar(50) = NULL
, @ThemeDesigner varchar(50) = NULL
, @Keywords varchar(50) = NULL
, @Page int = 1
, @RecsPerPage int = 15
)
AS
.
.
.
— for all varchars
SET @ModelNumber = ‘%’ + @ModelNumber + ‘%’
.
.
.
INSERT INTO #TempItems
SELECT ProductID
FROM Products
WHERE ProductID = @ProductID
OR
ModelNumber LIKE COALESCE(@ModelNumber, ModelNumber) AND Disable IS NULL
OR
.
.
.
Now you can call your procedure with the parameters you want: EXEC dbo.my_sproc @ProductID = 1, @ModelNumber = ‘xxx’ Since you are no longer doing insert exec, you should also consider using table datatype. Let me know if I’m in the right direction. Bambola.

hmmm…… All I’m trying to do in that exec statement is selectany productId where any part of the product description or whatever matches/includes the search criteria. The user could enter a product ID or just a keyword and the search would return all matches. Perhaps where I am going wrong is trying to migrate the existing select statement I am using into the SP. The select statement works on it’s own….it’s just when used in conjunction with the sp that it bombs.

Soo….Bambola….
With your code suggestion I can give the users the option to search by product id, model name, description etc…..very interesting…. but what I’m trying to do is just a general keywords search…most users wouldn’t know what field to search.

I think Bambola is taking you in the right direction. Perhaps if you can post the original query that you are using currently, things will become better. There has been another discussion on building Generic Search SP-
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=624 HTH. Gaurav

Thanks. I’ll check that link. This sp works: (
@Search nvarchar(255)
)
AS
SELECT
*
FROM
Products
WHERE
ModelNumber LIKE ‘%’ + @Search + ‘%’ AND Disable IS NULL
OR
ModelName LIKE ‘%’ + @Search + ‘%’ AND Disable IS NULL
OR
Description LIKE ‘%’ + @Search + ‘%’ AND Disable IS NULL
OR
ThemeDesigner Like ‘%’ + @Search + ‘%’ AND Disable IS NULL
OR
ProductID LIKE ‘%’ + @Search + ‘%’ AND Disable IS NULL
OR
Keywords LIKE ‘%’ + @Search + ‘%’ AND Disable IS NULL
ORDER BY ModelName

Please post the error that you are getting in the first SP.
Gaurav

Could you post the call to the procedure, the error message and the table structure?
It would also be helpful if you post example of search strings you wish to run. In the meantime, try to print the string you are constructing (from the application and sproc), see what you have there. Maybe try to run it in QA if you cannot figure out what the error is. If you stick with the exec (string), I’d recoomand using sp_execute(sql) with parameters. it is more secure and should perform better. Bambola.

Very true Bambola, sp_executesql() performs better than EXECUTE as its execution plan is cached while in the case of Execute, it is not. Gaurav Edit: Corrected the mistake as suggested by Bambola

I obviously meant sp_executesql. Bambola.

calling the procedure this way: SaleSQL = "sp_simplesearch " & currentpage &", "& iRecordsPerPage &", ‘"& SearchText&"’" Error:
Line 2 = Incorrect syntax near ‘WhateverWordEnteredInSearch’

Does this procedure runs fine from query analyzer? Gaurav

no. Same error either through the query analyzer or the script.

Post the complete SP and the input data for the same. I think we can work this out. Gaurav

Check to see if you have ‘ in the search string. It is also possible that one of the int variable is empty. If not, post the actualy string, what you run in QA, otherwise it is hard to tell what the problem is. (replace whatever it is that you don’t want us to see <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Bambola.

That is the whole SP. I use the following to test @Page = 1
@RecsPerPage =10
@Search = business I know it’s probably something simply you all can see….but it’s had me going in circles for 3 days!

Very crude question, are you passing business in quotes or not? If the ProdID is int as you have stored in temporary table, then u’ll have to cast it to varchar and then concatenate with % for the comparison. So the query becomes
INSERT INTO #TempItems (ProductID)
EXEC(‘SELECT ProductID FROM Products WHERE
ModelNumber LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
ModelName LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
Description LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
ThemeDesigner Like %’ + @Search + ‘% AND Disable IS NULL
OR
ProductID LIKE %’ + Convert(varchar(6),@Search) + ‘% AND Disable IS NULL
OR….
This brings us to the original point made by Bambola, if there is a datatype mismatch while comparison,u’ll have to cast them. So if rest of the datatypes are also not the same, cast them before using. Makke this change and ket us know. Gaurav

I assume it is @Search = ‘business’, otherwise it would have given an invalid column name error. So uhmm… you must be missing some key word. Try this: replace this:
INSERT INTO #TempItems (ProductID)
EXEC(‘SELECT ProductID FROM Products WHERE
ModelNumber LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
ModelName LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
Description LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
ThemeDesigner Like %’ + @Search + ‘% AND Disable IS NULL
OR
ProductID LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
Keywords LIKE %’ + @Search + ‘% AND Disable IS NULL
ORDER BY ModelName ‘ ) with this declare @str varchar(8000)
select @str = ‘SELECT ProductID FROM Products WHERE
ModelNumber LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
ModelName LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
Description LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
ThemeDesigner Like %’ + @Search + ‘% AND Disable IS NULL
OR
ProductID LIKE %’ + @Search + ‘% AND Disable IS NULL
OR
Keywords LIKE %’ + @Search + ‘% AND Disable IS NULL
ORDER BY ModelName ‘ PRINT @str
RETURN Now call it from QA and let me know what did it print. Bambola.

Bambola….replacing the code as directed returns "The stored procedure executed successfully but did not return any records" I’ve tried passing the search word with quotes and without.

What was the outut of the statement PRINT @str? And if it is not showing any output, then probably you passed @Search as NULL. Gaurav

It is not supposed to return records.
ok, replace PRINT @sql with SELECT @sql Bambola.

There was no output….not that I could see. How could I possibly be passing the @Search as NULL??

Ok Bambola….that gives me: SELECT ProductID FROM Products WHERE
ModelNumber LIKE %business% AND Disable IS NULL
OR
ModelName LIKE %business% AND Disable IS NULL
OR
Description LIKE %business% AND Disable IS NULL
OR
ThemeDesigner Like %business% AND Disable IS NULL
OR
ProductID LIKE %business% AND Disable IS NULL
OR
Keywords LIKE %business% AND Disable IS NULL
ORDER BY ModelName

You are missing the quotes. it should be SELECT ProductID FROM Products WHERE
ModelNumber LIKE ‘%business%’ AND Disable IS NULL
OR
ModelName LIKE ‘%business%’ AND Disable IS NULL
OR
Description LIKE ‘%business%’ AND Disable IS NULL
OR
ThemeDesigner Like ‘%business%’ AND Disable IS NULL
OR
ProductID LIKE ‘%business%’ AND Disable IS NULL
OR
Keywords LIKE ‘%business%’ AND Disable IS NULL
ORDER BY ModelName Copy this from here into QA, and change back your sproc. Does it still give you an error? Bambola.

GENIUS! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] This did the trick: INSERT INTO #TempItems (ProductID) EXEC(‘SELECT ProductID FROM Products WHERE ModelNumber LIKE ”%’ + @Search + ‘%” AND Disable IS NULL OR ModelName LIKE ”%’ + @Search + ‘%” AND Disable IS NULL OR Description LIKE ”%’ + @Search + ‘%” AND Disable IS NULL OR ThemeDesigner Like ”%’ + @Search + ‘%” AND Disable IS NULL OR ProductID LIKE ”%’ + @Search + ‘%” AND Disable IS NULL OR Keywords LIKE ”%’ + @Search + ‘%” AND Disable IS NULL ORDER BY ModelName ‘ ) I cannot thank you enough for your help! You’ve taught me some great things about debugging today, and I really appreciate it!

The only problem now becomes if they enter more than one word into the search. Any ideas???

That problem can be solved. You can write a function that returns a table datatype, and join it in your query. Inside the function, parse the string accorsing to a delimiter, and insert each value into the table datatype. Something like CREATE FUNCTION SplitString (@string varchar(8000), @delimiter char(1))
RETURNS @elements TABLE (Element varchar(8000)) — or any other datatype
AS
BEGIN
— here write your code to loop over the string and insert each value into
— the table datatype. You can check for duplications if you want (or add it as
— a parameter of the function) RETURN
END Now your query will look something like: SELECT columns…
FROM my_table INNER JOIN dbo.SplitString(@str, ‘,’)
ON column_to_compare = Element OR SELECT columns…
FROM my_table
WHERE column_to_compare IN (SELECT Element FROM dbo.SplitString(@str, ‘,’) ) Bambola.

Thanks! I really appreciate the help. I couldn’t have done it without you.

]]>