stored procedures

[vlitim]

No I am probably doing this completely wrong anyway but..........I am doing a search and I have multiple tables with different columns that need searching. What I want to do is add them to a temp table and then get them back in order by name. Now this seems to put the correct info into the temp table, but I can't get it to return the results in the correct order! any help would be great



CREATE PROCEDURE sp_MainSearch
(
@searchterm varchar(100),
@Page int,
@RecsPerPage int
)
AS

SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
name varchar(255),
PID int,

)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (name,pid)
SELECT name,id
FROM products
WHERE name LIKE '%'+@searchterm+'%' OR description LIKE '%'+@searchterm+'%'

INSERT INTO #TempItems (name,pid)
SELECT name,id
FROM offices
WHERE name LIKE '%'+@searchterm+'%' OR address LIKE '%'+@searchterm+'%'

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
,AllRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec ORDER by name



-- Turn NOCOUNT back OFF
SET NOCOUNT OFF



Cheers Tim

 

[tlbroadbent]

Try this modification to the INSERT statements to sort the data as inserted into the temp table.

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (name,pid)
SELECT name,id
FROM products
WHERE name LIKE '%'+@searchterm+'%' OR description LIKE '%'+@searchterm+'%'
UNION
SELECT name,id
FROM offices
WHERE name LIKE '%'+@searchterm+'%' OR address LIKE '%'+@searchterm+'%'
OORDER BY name Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[vlitim]

ok now another question, ist there anyway of inserting an identifier into the temptable so that you know which table the record came from??

 

[tlbroadbent]

Yes, you can insert a constant.

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (name,pid,tblname)
SELECT name,id,'products'
FROM products
WHERE name LIKE '%'+@searchterm+'%' OR description LIKE '%'+@searchterm+'%'
UNION
SELECT name,id,'offices'
FROM offices
WHERE name LIKE '%'+@searchterm+'%' OR address LIKE '%'+@searchterm+'%'
ORDER BY name Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

&quot;The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge.&quot; - Daniel J Boorstin

 

[vlitim]

Excellent thanks for all your help on this!

 

[vlitim]

As we are on the subject of searching........

two things I have never been able to figure out

1. how to do AND/OR searches
2. how to order a search by relavence.

Does anyone no a good site that explains either of these?

cheers