(DML below)
First, thank you in advance for your help.  Here's one for those of you that
like a little challenge, or perhaps this is a cake walk for you (I'm jealous).

This DB is to hold information so that our helpdesk can search it for
support information.  The front end will be a Visual Basic application
calling stored procedures.

Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
document.  We will Full Text Indexed both of those columns so as to enable a
granular search.  I'm guessing the "freetext" t-sql command is the tool for
this job to create a search engine like stored procedure.(if there's a better
way, please suggest it).

The Front End VB application will have the following interface, so the
Stored Procedure will need to accomodate the following:

1) A text box to enter the search terms which searches the
tblDocument.document and tblSystem.Document columns for words that are
entered.

2) Dropdown box: Choice between a System Document (from tblSystem), or a
support document (from tblDocument).  If System is choosen, it only searches
tblSystem.  If Document is choosen, it only searches tblDocument.  If left
blank will search both.

3) Dropdown box: Choice of a category to search (from  tblcatagories), If a
category is choosen, the stored procedure will only return those documents
meeting that criteria.  if left blank will search all categories.

4) Dropdown box:  Choice of a SUBCategory to search (from tblsubcategories),
If Subcategory is choosen, the stored procedure will only return those
documents meeting that criteria.  if left blank will search all SUBcategories.

5) Dropdown box: Ability to choose whom submitted the document.  If
SubmitterName is choosen, the stored procedure will only return thos document
OR systems meeting that criteria.  If left blank will search all systems AND
documents from all submitters.


The Stored Procedure will need to return results that look like this:

(if search terms hit a document) DocumentID, Title, SubmitterName
(if search terms hit a system) SystemID, SystemName, SubmitterName


Thanks again
Jeff



CREATE DATABASE [eDOC]  ON (NAME = N'eDOC_Data', FILENAME =
N'C:\MSSQL7\Data\eDOC_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME =
N'eDOC_Log', FILENAME = N'C:\MSSQL7\Data\eDOC_Log.LDF' , SIZE = 1, FILEGROWTH
= 10%)

GO

exec sp_dboption N'eDOC', N'autoclose', N'false'
GO

exec sp_dboption N'eDOC', N'bulkcopy', N'false'
GO

exec sp_dboption N'eDOC', N'trunc. log', N'false'
GO

exec sp_dboption N'eDOC', N'torn page detection', N'true'
GO

exec sp_dboption N'eDOC', N'read only', N'false'
GO

exec sp_dboption N'eDOC', N'dbo use', N'false'
GO

exec sp_dboption N'eDOC', N'single', N'false'
GO

exec sp_dboption N'eDOC', N'autoshrink', N'false'
GO

exec sp_dboption N'eDOC', N'ANSI null default', N'false'
GO

exec sp_dboption N'eDOC', N'recursive triggers', N'false'
GO

exec sp_dboption N'eDOC', N'ANSI nulls', N'false'
GO

exec sp_dboption N'eDOC', N'concat null yields null', N'false'
GO

exec sp_dboption N'eDOC', N'cursor close on commit', N'false'
GO

exec sp_dboption N'eDOC', N'default to local cursor', N'false'
GO

exec sp_dboption N'eDOC', N'quoted identifier', N'false'
GO

exec sp_dboption N'eDOC', N'ANSI warnings', N'false'
GO

exec sp_dboption N'eDOC', N'auto create statistics', N'true'
GO

exec sp_dboption N'eDOC', N'auto update statistics', N'true'
GO

use [eDOC]
GO

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'

GO

if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'

GO

CREATE TABLE [dbo].[tblCategories] (
    [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
    [CategoryName] [char] (100) ,
    [CreattionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblDocument] (
    [DocumentID] [int] IDENTITY (1000, 1) NOT NULL ,
    [CategoryID] [int] NOT NULL ,
    [SubCategoryID] [int] NOT NULL ,
    [Title] [char] (100)  ,
    [KeyWords] [char] (100)  ,
    [SubmitterID] [int] NOT NULL ,
    [CreationDate] [datetime] NULL ,
    [RevisedBy] [int] NOT NULL ,
    [RevisedDate] [datetime] NULL ,
    [Document] [text]  NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSubCategories] (
    [SubCategoyID] [int] IDENTITY (1, 1) NOT NULL ,
    [CategoryID] [int] NOT NULL ,
    [SubcategoryName] [char] (100)  NOT NULL ,
    [CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSubmitter] (
    [SubmitterID] [int] IDENTITY (1, 1) NOT NULL ,
    [SubmitterName] [char] (40)  NOT NULL ,
    [Username] [varchar] (20)  NULL ,
    [Password] [varchar] (20)  NULL ,
    [CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSystem] (
    [SystemID] [int] IDENTITY (10000, 5) NOT NULL ,
    [SubmitterID] [int] NOT NULL ,
    [SystemName] [char] (100)  NOT NULL ,
    [RevisedDate] [datetime] NOT NULL ,
    [RevisedBy] [char] (100)  NOT NULL ,
    [Document] [text]  NOT NULL ,
    [CreationDate] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
    CONSTRAINT [PK_tblCategories] PRIMARY KEY  CLUSTERED
    (
        [CategoryID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
    CONSTRAINT [PK_tblDocument] PRIMARY KEY  CLUSTERED
    (
        [DocumentID]
    )  ON [PRIMARY]
GO


USE edoc

EXEC sp_fulltext_database 'enable'

go

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1
exec sp_fulltext_database N'enable'

GO

if not exists (select * from dbo.sysfulltextcatalogs where name =
N'DocumentFullText')
exec sp_fulltext_catalog N'DocumentFullText', N'create'

GO

exec sp_fulltext_table N'[dbo].[tblDocument]', N'create',
N'DocumentFullText', N'PK_tblDocument'
GO

exec sp_fulltext_column N'[dbo].[tblDocument]', N'Document', N'add', 1033 
GO

exec sp_fulltext_table N'[dbo].[tblDocument]', N'activate' 
GO

ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
    CONSTRAINT [PK_tblSubCategories] PRIMARY KEY  CLUSTERED
    (
        [SubCategoyID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
    CONSTRAINT [PK_tblSubmitter] PRIMARY KEY  CLUSTERED
    (
        [SubmitterID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
    CONSTRAINT [PK_tblSystem] PRIMARY KEY  CLUSTERED
    (
        [SystemID]
    )  ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
    CONSTRAINT [DF_tblCategories_CreattionDate] DEFAULT (getdate()) FOR
[CreattionDate]
GO

ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
    CONSTRAINT [DF_tblDocument_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO

ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
    CONSTRAINT [DF_tblSubCategories_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO

ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
    CONSTRAINT [DF_tblSubmitter_CreationDate] DEFAULT (getdate()) FOR
[CreationDate]
GO

ALTER TABLE [dbo].[tblSystem] WITH NOCHECK ADD
    CONSTRAINT [DF_tblSystem_RevisedDate] DEFAULT (getdate()) FOR [RevisedDate],
    CONSTRAINT [DF_tblSystem_CreationDate] DEFAULT (getdate()) FOR [CreationDate]
GO

ALTER TABLE [dbo].[tblDocument] ADD
    CONSTRAINT [FK_tblDocument_tblCategories] FOREIGN KEY
    (
        [CategoryID]
    ) REFERENCES [dbo].[tblCategories] (
        [CategoryID]
    ),
    CONSTRAINT [FK_tblDocument_tblSubCategories1] FOREIGN KEY
    (
        [SubCategoryID]
    ) REFERENCES [dbo].[tblSubCategories] (
        [SubCategoyID]
    ),
    CONSTRAINT [FK_tblDocument_tblSubmitter2] FOREIGN KEY
    (
        [SubmitterID]
    ) REFERENCES [dbo].[tblSubmitter] (
        [SubmitterID]
    )
GO

ALTER TABLE [dbo].[tblSubCategories] ADD
    CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
    (
        [CategoryID]
    ) REFERENCES [dbo].[tblCategories] (
        [CategoryID]
    )
GO

ALTER TABLE [dbo].[tblSystem] ADD
    CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
    (
        [SubmitterID]
    ) REFERENCES [dbo].[tblSubmitter] (
        [SubmitterID]
    )
GO


use edoc
go

INSERT into tblcategories(CategoryName) VALUES ('hardware')
insert into tblcategories(CategoryName) values ('Software')

insert into tblsubcategories(categoryID,subcategoryname) values ('1','Dell
Server')
insert into tblsubcategories(categoryID,subcategoryname) values
('2','Outlook')


insert into tblsubmitter(submittername, username, [password]) values ('Joe
Williams', 'WilliamsJ', 'password')
insert into tblsubmitter(submittername, username, [password]) values ('Mike
Smith', 'SmithM', 'password')

insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('1', 'SQL Server', '2', 'Long HTML document')
insert into tblsystem(submitterID, SystemName, RevisedBy, Document) values
('2', 'Exchange', '1', 'Very Long HTML document')

insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('1', '2', 'Outlook Crashes',
'run32.dll abend', '1', '2', 'Outlook document goes here')
insert into tbldocument(categoryID, subcategoryID, Title, Keywords,
submitterID, Revisedby, document) values ('2', '1', 'Dell Post Error 151',
'0x8765432', '2', '1', 'Dell  document goes here')

go

On Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:

Hi Jeff,

Thanks for supplying DDL and sample data!

Prefixing table names with "tbl" is considered a bad habit in the world
of relational databases. I'd rename these tables "Systems" and
"Documents" (since they store information about more than one system and
more than one document).

I have no experience with full text indexing. From the sound of it, it's
probably the tool for the job, though.

Note that there is a special group where the full text experts prefer to
hang their hats: microsoft.public.sqlserver.fulltext.

That would be a job for full text search, I guess.

My choice would be to have two stored procedures. One searches the
Systems table, the other searches the Documents table. The front end
uses this dropdown box to decide whether to call the first, the second,
ot both and merge the results in the front end. (The added advantage is
that your front end has no trouble figuring out if a returned row refers
to a system or to a document).

For all these three requirements: www.sommarskog.se/dyn-search.html.

(big snip)
* If not all categories have names with a length of >90 characters,
change the datatype to varchar(100).
* An identity can never be the only key of a table. You'll need a
natural key as well, to prevent duplicates. Assuming that no two
categories share the same name, add a UNIQUE constraint on CategoryName.
* Oh, and fix the typo in CreattionDate before deploying your system. It
is still an easy fix now.
* Why is there no NOT NULL constraint for CategoryName?

Show quoteHide quote* Change char to varchar in this table as well (and in the remainging
tables of course - I won't repeat this advice)
* Since there are no tables that refer to this table, there's no need
for a surrogate key. You should remove the identity column.
* I don't know your data well enough to propose what set of columns make
up the business key in your business, but I'm sure you do. Maybe just
"Title"? Maybe the combination of Category, Subcategory and Title?
* If subcategory S1 belongs to Category C1, is it then possible for a
document to belong to subcategory S1, but to category C2? If not, then
the column CategoryID should not be in this table at all, unless you
have compelling reasons (probably performance-driven) to use a partly
denormalized design - and in that case, you should probably add some
extra constraints to make sure that you'll never get any data like the
example I gave. (Suggestion: use a foreign key on (Category,
Subcategory) - you'll have to create a "redundant" UNIQUE constraint in
the Subcategories table for that)
* Why is there no NOT NULL constraint for Title, Keywords, and Document?
* Why is there a NOT NULL constraint for RevisedBy, but none for
RevisedDate? I think RevisedBy shouyld be NULLable as well?
* Why is there no foreign key constraint for RevisedBy?

Show quoteHide quote* Assuming that two subcategories of the same category can't have the
same name, but two subcategories of different categories can, the real
key is (CategoryID, SubcategoryName). Use a UNIQUE constraint to prevent
duplicates.

Show quoteHide quote* Storing passwords in a table is not really safe.
* The UNIQUE constraint should go on SubmitterName, I guess.
* Why is there no NOT NULL constraint for Username and Password?

Show quoteHide quote* Most of the comments that relate to the Documents table relate to this
table as well. I won;t repeat them here.
* Why the non-standard seed and increment for the IDENTITY property? Is
this table part of some distributed replication setup?
* Why did you define RevisedBy as int in the Documents table, but as
char(100) in this table? Having two columns with the sme name but with
different datatypes (and different contents) will cause you a wealthy
supply of bugs!

Best, Hugo

Best, Hugo

I really appreciate your input.  In short to your comments, some were errors,
some are on my to-do list, like adding the constraints, some are due to the
evoluation of the DB and we need to go back and fix, and the rest are things
that I aparently need to further learn.

And I especially appreciate the advice on the best way to handle the stored
procedure which I where I'm stuck since DBs are not my strong point as you
can tell.  I'm not a programmer, I'm a newtorking guy that only dabbles in
this.


But where I'm really at a loss is the stored procedures themselves.  So how
can I turn the below basic procedure into one that will accept the various
options and return results.  Obviously, as it stands now, I can't leave a
variable blank....

@searchterm must have something entere in it
@Category must accept either a 1, 2, or be left blank to return both 1 & 2.
@Subcategory  same as above
@Submitter same as above

Thanks again in advance for all your help!

CREATE  PROCEDURE SearchDocument

(
@SearchTerm char (200),
@Category int,
@Subcategory int,
@Submitter int
)

as


SELECT dbo.tblDocument.DocumentID,
       dbo.tblDocument.Title,
       dbo.tblSubmitter.SubmitterName

FROM  dbo.tblDocument join dbo.tblSubmitter
      on (tbldocument.submitterid = tblsubmitter.submitterid)

where freetext    (document, @searchterm) and
        dbo.tblDocument.SubCategoryID = @subcategory  AND
        dbo.tblDocument.CategoryID = @category  AND
        dbo.tblDocument.SubmitterID = @submitter


Show quoteHide quote

Jeff (J***@discussions.microsoft.com) writes:
You cannot leave it blank, but you can set it to NULL, and you can even make
that a default value:

   @SearchTerm char (200),
   @Category int = NULL,
   @Subcategory int = NULL,
   @Submitter int = ULL

Then you can say

   AND (dbo.tblDocument.SubCategoryID = @subcategory OR @subcategory IS NULL

Note that if any of the columns are indexed, those indexes will not be
used. If you want an index on, say, CategoryID to be used, you will
need to use IF statements that leads to different SELECT statements,
or use dynamic SQL.

On Sun, 11 Sep 2005 11:22:04 -0700, Jeff wrote:

(snip)
Hi Jeff,

I attempted to answer that question by providing a link to Erland's page
about this: http://www.sommarskog.se/dyn-search.html. Sure, it's a long
read, but (IMO) well worth the time you spend reading it. You'll find
that Erland presents many ways to achieve what you are trying to do
(searching with optional search arguments), and elaborates on the pros
and cons of all the various methods.

I see that Erland now has also posted a solution taht uses one of the
method's described on his page. I trust that this was enough to get you
going (but feel free to ask again if it's not!).

But I still recommend that you take the time to read the complete
article - as I said: time well spent!!

Best, Hugo

Oh yes!  I remember that trick of setting the variables to null now...

Thanks!!

Show quoteHide quote

Jeff,
So, the column document  "freetext (document, @searchterm)" is the only
column that is FT-enabled. Correct?
Could you provide more details on the SQL Server version (@@version) as well
as the OS platform, and language of text in the document column? As all of
these variables are important for functionality as is the number of rows in
your FT-enabled table.

Thanks,
John
Show quoteHide quote

Have you looked into a Help Desk package instead of writing your own?

HowTo:Dynamically Inject a Select Query Into UDF
How to write results of a sp/query to multiple .csv/xls files
What is SQL Server version of Oracle's CPU_PER_CALL, or how do I restrict max. exec. time of a query
SImple code error
How Do I Avoid Update Deadlock