Query Repitition

bmains · August 5th, 2004, 12:15 PM

Hello,

Here's my issue that I am having in my procs. I have a proc that uses a query similar to this:

select [ID], [Name], [Description] from tblProducts

I have several parameters that filter the results of this query, which are:

@Name varchar(50),
@CategoryName varchar(50),
@MatchExactly bit

The name filters the query by name (not necessarily matching exactly). I'm using Full-Text indexing so I would like to use where Contains(Name, @Name), which works fine. The issue is, I can't figure out a way to filter by category name, and being able to match exactly. I have the proc up with four scenarios:

if (match exactly is one)
  if (catgory name isn't null)
    run proc that inner joins with tblCategories
  else
    run proc without tblCategories (if I inner join with this,
    produces null)

else
  if (category name isn't null)
    run proc that uses full-text indexing and inner joins with
    tblCategories table
  else
    run proc that uses full-text indexing without inner join

I don't want to have 4 separate queries, but will if I have to. Any ideas? I understand that the match exactly scenario has to exist, but is there anyway that I can avoid two separate queries because of the category name? Will I have to look at an outer join?

Thanks,

Brian

bmains · August 6th, 2004, 09:34 AM

Hello,

Basically, it looks like this (I said category name before, but I changed it to category ID; makes it a little easier):

if (@MatchExactly = 1)
begin
  if (not @CategoryID is NULL)
    select p.[ID], p.[Name], p.[Description] from tblProducts p
    inner join tblProductsCategories c on p.[ID] = c.[ProductID]
    where p.[Name] = @Name and c.[CategoryID] = @CategoryID
  else
    select p.[ID], p.[Name], p.[Description] from tblProducts p
    where p.[Name] = @Name
end

else
begin
  if (not @CategoryID is NULL)
    select p.[ID], p.[Name], p.[Description] from tblProducts p
    inner join tblProductsCategories c on p.[ID] = c.[ProductID]
    where Contains(p.[Name], @Name) and c.[CategoryID] = @CategoryID
  else
    select p.[ID], p.[Name], p.[Description] from tblProducts p
    where Contains(p.[Name], @Name)
end

Any ideas?

happygv · August 6th, 2004, 10:18 PM

Hi Brian,

One uses freetext indexing decided by @MatchExactly, the other involves with 2 tables decided by @CategoryID. So I don't think you can reduce that by 2 queries.

This is the only way I can think of. I have reversed the If conditions from your code. Not much of change there.

Code:

Declare @strSql varchar(500)

if (not @CategoryID is NULL)
begin
  Set @strSql = 'select p.[ID], p.[Name], p.[Description] from tblProducts p'
  Set @strSql = @strSql + ' inner join tblProductsCategories c on p.[ID] = c.[ProductID]'
  Set @strSql = @strSql + ' where c.[CategoryID] = ' + @CategoryID + ' and'
  if (@MatchExactly = 1)
    Set @strSql = @strSql + ' p.[Name] = ' + @Name
  else
    Set @strSql = @strSql + ' Contains(p.[Name],' + @Name + ')'
end

else
begin
  Set @strSql = 'select p.[ID], p.[Name], p.[Description] from tblProducts p where'
  if (@MatchExactly = 1)
    Set @strSql = @strSql + ' p.[Name] = ' + @Name
  else
    Set @strSql = @strSql + ' Contains(p.[Name],' +  @Name + ')'
end

exec(@strSql)

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

bmains · August 9th, 2004, 07:35 AM

Hey,

I had thought of that, but was trying to get away from the dynamic SQL, because of problems that I had in the past. Thanks for that though.

Brian

pgtips · August 10th, 2004, 07:47 AM

You could use the trick of COALESCE-ing away the NULL CategoryID, like this:

if (@MatchExactly = 1)
begin
    select p.[ID], p.[Name], p.[Description] from tblProducts p
    inner join tblProductsCategories c on p.[ID] = c.[ProductID]
    where p.[Name] = @Name and c.[CategoryID] = COALESCE(@CategoryID, c.[CategoryID])
end

else
begin
    select p.[ID], p.[Name], p.[Description] from tblProducts p
    inner join tblProductsCategories c on p.[ID] = c.[ProductID]
    where Contains(p.[Name], @Name) and c.[CategoryID] = COALESCE(@CategoryID, c.[CategoryID])
end