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
 Old Forums
 CLOSED - General SQL Server
 Search dilema
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

jazzym
Yak Posting Veteran

United Kingdom
94 Posts

Posted - 07/21/2004 :  04:08:59  Show Profile  Visit jazzym's Homepage
I've created my own forum system as we have very specific needs. I'm currently working on the search feature of the forums and have come across a problem that i've yet to solve. I was wondering if SQL may be the way forward.

My problem is that my search feature must be able to search through topic posts and replies to topics. Returning the title of the original topic and it's ID is no problem. The problem i do have is that if you set very unspecific criteria you get back results that include both the original topic and also replies to that topic. They all link to the same topic so it's a waste to see them all. what i need is to just show it once. Whether it's a reply or the original topic it doesn't matter as the same data is display, linking to the topic.

My first approach was an array in ASP which was checked to see if it contained the topic ID alreayd, meaning it had already output it, if not found it would output and store the topic ID in the array.

This whole looping inside i loop since seriously inefficient. I was wondering if there is anyway i could deal with this problem in SQL. At present i have a view which joins together all the data i need for my search results, then when i call the view i just add a few WHERE clauses to filter. Straight forward stuff at present.

If you need any more info just ask. Any ideas much appreciated.

JasonGoff
Posting Yak Master

United Kingdom
158 Posts

Posted - 07/21/2004 :  04:38:21  Show Profile
Post the schema of your topic tables and some sample data.
Go to Top of Page

jazzym
Yak Posting Veteran

United Kingdom
94 Posts

Posted - 07/21/2004 :  04:45:16  Show Profile  Visit jazzym's Homepage
a basic idea would be a simple messages table

ID,title, message,originator_id

originator_id is for replies, 0 if topic and ID of topic if replies.

Simple


As for sample data. i dont really have any.
Go to Top of Page

JasonGoff
Posting Yak Master

United Kingdom
158 Posts

Posted - 07/21/2004 :  04:54:09  Show Profile

DECLARE @SearchResults TABLE
   (ID INT,
    Title VARCHAR(100),
    Message VARCHAR(6000)
   )

-- Topic items
INSERT INTO @SearchResults
   SELECT ID, Title, Message
   FROM MyTable
   WHERE Originator_ID=0 AND (Title LIKE '%SEARCHSTRING%' OR Message LIKE '%SEARCHSTRING%')

-- Replies not in the topics already selected
INSERT INTO @SearchResults
   SELECT ID, Title, Message
   FROM MyTable
   WHERE (Title LIKE '%SEARCHSTRING%' OR Message LIKE '%SEARCHSTRING%')
   AND NOT EXISTS (SELECT 1 FROM @SearchResults WHERE ID=MyTable.Originator_ID)

--Return all the Results
SELECT * FROM @SearchResults
Go to Top of Page

jazzym
Yak Posting Veteran

United Kingdom
94 Posts

Posted - 07/21/2004 :  06:14:18  Show Profile  Visit jazzym's Homepage
Works a treat, with a slight tweak to the second INSERT statement as it only needs to search through replies, not all records. Thats sorted.

My next problem is that i build a long where clause in my ASP page for filtering the records. I was told that you can store an SQL statement in a variable then execute the variable.

SET @sql = 'SELECT * FROM table'
EXEC @sql


So i am sending my already generated where clause to the store procedure and was hoping to do this.

-- Topic items
SET @sql = '
INSERT INTO @SearchResults
   SELECT ID, Title, Message
   FROM MyTable
   WHERE Originator_ID=0 ' + @where_clause

EXEC(@sql)


The error i get is "Must declare the variable '@SearchResults'".

I take it there is a problem with storing data into variables when code is executed this way?

Any ideas?

Sorry i forgot to mention that i had declared the vairable as mentioned in the code you supplied. SO it's nothing obvious like that.

Edited by - jazzym on 07/21/2004 06:19:30
Go to Top of Page

JasonGoff
Posting Yak Master

United Kingdom
158 Posts

Posted - 07/21/2004 :  06:41:06  Show Profile
Ah, the problem here is one of scope. Variables are only in scope for the process in which they run. Dynamic SQL like that in your code executes in it's own scope and can't see the table variable declared in the calling proc.
Thinking on a solution that doesn't need the table...watch this space.
Go to Top of Page

JasonGoff
Posting Yak Master

United Kingdom
158 Posts

Posted - 07/21/2004 :  06:44:12  Show Profile
OK, one solution is to put the whole thing in a dynamic statement (a bit messy). For example...


Declare @cmd varchar(6000)
SELECT @cmd='
declare @t table
(dt datetime)
INSERT INTO @t VALUES(GETDATE())
SELECT * FROM @t'
exec (@cmd)
Go to Top of Page

JasonGoff
Posting Yak Master

United Kingdom
158 Posts

Posted - 07/21/2004 :  06:47:25  Show Profile
Re-reading, is it possible to only pass the search string through, as opposed to the entire WHERE clause. If so, it means a query plan can be stored for the proc, making it far more efficient than executing dynamic SQL. Just a thought..
Go to Top of Page

jazzym
Yak Posting Veteran

United Kingdom
94 Posts

Posted - 07/21/2004 :  06:51:29  Show Profile  Visit jazzym's Homepage
I currently have 5 criteria and 4 types of searching of keywords (any word, all words, exact match)

Either way i'd need to build up a large where clause. Whether i get it from the ASP page as a variable or build a variable in the sproc. It's still a variable.

Go to Top of Page

JasonGoff
Posting Yak Master

United Kingdom
158 Posts

Posted - 07/21/2004 :  06:53:22  Show Profile
And here's a pure set-based solution...


   SELECT ID, Title, Message
   FROM MyTable
   WHERE Originator_ID=0 AND (Title LIKE '%SEARCHSTRING%' OR Message LIKE '%SEARCHSTRING%')
   UNION ALL
   SELECT A.ID, A.Title, A.Message
   FROM MyTable A LEFT JOIN MyTable B ON A.Originator_ID=B.ID
   WHERE B.ID IS NULL
   AND A.Originator_ID > 0
   AND (A.Title LIKE '%SEARCHSTRING%' OR A.Message LIKE '%SEARCHSTRING%')

Go to Top of Page

jazzym
Yak Posting Veteran

United Kingdom
94 Posts

Posted - 07/21/2004 :  07:03:40  Show Profile  Visit jazzym's Homepage
I put the original code you gave me (or at least the concept that you gave me) inside a variable. So all of my original sproc was inside the variable. I then added the @where_clause values in the right places. Effectively turning it into one big string which i then executed.

Worked a treat. Everything in scope.

Thanks for your help! It's awesome!
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/21/2004 :  13:48:47  Show Profile
-- Topic items
SET @sql = '
INSERT INTO @SearchResults
   SELECT ID, Title, Message
   FROM MyTable
   WHERE Originator_ID=0 ' + @where_clause

EXEC(@sql)

What you need here is sp_executeSQL, rather than EXEC. This little chappie takes parameters AND stores the (parameterised) query and its execution plan in the cache - so it should run pretty much as fast as a stored procedure subsequently.

You can't use dynamic SQL to store into a table variable though.

So you would need to use

SELECT @where_clause = 'MyColumn1 = @MyCol1 AND MyColumn2 = @MyCol2'
SELECT @sql = '
   SELECT ID, Title, Message
   FROM MyTable
   WHERE Originator_ID=0 ' + @where_clause

INSERT INTO #MyTempTable
EXEC sp_ExecuteSQL(@sql, 
          N'@MyCol1 varchar(10), @MyCol2 int, @MyCol3 float', 
          @MyCol1, @MyCol2, @MyCol3)

Note that I have used "@MyCol3" in the sp_ExecuteSQL even though it isn't referenced. In this way you can pass all the POSSIBLE parameters to sp_ExecuteSQL, even if the dynamic part of the SQL doesn't always use them all. SQL Server caches the Statement (1st parameter) to sp_ExecuteSQL, so its obviously important that it doesn't change all the time - but once the WHERE clause is parameterised its likely to only be one of a few possible choices, hence the efficiency gain.

(I haven't tested this code, so its only a close approximation of what you actually need. Hopefully someone pedantic will be along in a minute to point out all my syntax errors!)

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000