A correlated SubQuery Procedure Problem!

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

SQL Server 2000 Forums

SQL Server Development (2000)

A correlated SubQuery Procedure Problem!

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2000-11-16 : 01:06:14

Hi there, If anyone can take the time have a look at my problem...

The following proc is called from an asp page as

sqltemp = "sp_ACTIVITIES_SEARCH " & StrCategoryID & "," & Strkeywords

---- now the procedure it is calling...

CREATE PROCEDURE sp_ACTIVITIES_SEARCH
@CatID int,
@KeyWords text
AS

-- This corelated subquery searches the FULL TEXT INDEX first and then pulls out any results to the main query.

SELECT activities.activity_id, activities.activity_name, activities.activity_blurb
FROM activity_categories
INNER JOIN activities
ON activities.activity_id = activity_categories.activity_id
WHERE activity_categories.category_id = @CatID
AND activities.activity_id IN
(Select activity_id from activities where CONTAINS (activities.activity_blurb, ' " @KeyWords " ')
OR CONTAINS (activities.activity_blurb, ' " @KeyWords " ')
OR activities.activity_name like '% @KeyWords % ')
AND activities.active = 1
ORDER BY activity_name

Now Im getting no results if calling this up from Query Analyser (sp_ACTIVITIES_SEARCH 3, ceasers) or the ASP page (as descripbed above) but if I hardcode the variables like this:::

SELECT activities.activity_id, activities.activity_name, activities.activity_blurb
FROM activity_categories
INNER JOIN activities
ON activities.activity_id = activity_categories.activity_id
WHERE activity_categories.category_id = 3
AND activities.activity_id IN
(Select activity_id from activities where CONTAINS (activities.activity_blurb, ' " ceasers " ')
OR CONTAINS (activities.activity_blurb, ' " ceasers " ')
OR activities.activity_name like '% ceasers % ')
AND activities.active = 1
ORDER BY activity_name

I get results??? Any ideas on why? the ceasers variable is the name supplied in the strkeywords variable and the no 3 is the ID of the category that the record should belong to.

Cheers - matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources