Challenging Search Engine Like Stored Porcedure Needed

(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 bette
r
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 SUBcategorie
s.
5) Dropdown box: Ability to choose whom submitted the document. If
SubmitterName is choosen, the stored procedure will only return thos documen
t
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, FILEGROWT
H
= 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,subcategoryn
ame) values ('1','Dell
Server')
insert into tblsubcategories(categoryID,subcategoryn
ame) 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')
goOn Sat, 10 Sep 2005 11:47:08 -0700, Jeff wrote:

>(DML below)
Hi Jeff,
Thanks for supplying DDL and sample data!

>Note "tblDocument.Document" and "tblSystem.Document" will hold an HTML
>document.
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).

> 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 bett
er
>way, please suggest it).
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.

>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.
That would be a job for full text search, I guess.

>2) Dropdown box: Choice between a System Document (from tblSystem), or a
>support document (from tblDocument). If System is choosen, it only searche
s
>tblSystem. If Document is choosen, it only searches tblDocument. If left
>blank will search both.
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).

>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 SUBcategori
es.
>5) Dropdown box: Ability to choose whom submitted the document. If
>SubmitterName is choosen, the stored procedure will only return thos docume
nt
>OR systems meeting that criteria. If left blank will search all systems AN
D
>documents from all submitters.
For all these three requirements: www.sommarskog.se/dyn-search.html.
(big snip)
>CREATE TABLE [dbo].[tblCategories] (
> [CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [CategoryName] [char] (100) ,
> [CreattionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblCategories] PRIMARY KEY CLUSTERED
> (
> [CategoryID]
> ) ON [PRIMARY]
>GO
* 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?

>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
(...)
>ALTER TABLE [dbo].[tblDocument] WITH NOCHECK ADD
> CONSTRAINT [PK_tblDocument] PRIMARY KEY CLUSTERED
> (
> [DocumentID]
> ) ON [PRIMARY]
>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
* 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?

>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
(...)
>ALTER TABLE [dbo].[tblSubCategories] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubCategories] PRIMARY KEY CLUSTERED
> (
> [SubCategoyID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSubCategories] ADD
> CONSTRAINT [FK_tblSubCategories_tblCategories] FOREIGN KEY
> (
> [CategoryID]
> ) REFERENCES [dbo].[tblCategories] (
> [CategoryID]
> )
>GO
* 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.

>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
(...)
>ALTER TABLE [dbo].[tblSubmitter] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSubmitter] PRIMARY KEY CLUSTERED
> (
> [SubmitterID]
> ) ON [PRIMARY]
>GO
* 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?

>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].[tblSystem] WITH NOCHECK ADD
> CONSTRAINT [PK_tblSystem] PRIMARY KEY CLUSTERED
> (
> [SystemID]
> ) ON [PRIMARY]
>GO
(...)
>ALTER TABLE [dbo].[tblSystem] ADD
> CONSTRAINT [FK_tblSystem_tblSubmitter1] FOREIGN KEY
> (
> [SubmitterID]
> ) REFERENCES [dbo].[tblSubmitter] (
> [SubmitterID]
> )
>GO
* 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
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||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
"Hugo Kornelis" wrote:

> 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?
>
> (...)
> (...)
> * 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?
>
> (...)
> (...)
> * 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.
>
> (...)
> * 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?
>
> (...)
> (...)
> * 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
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Jeff (Jeff@.discussions.microsoft.com) writes:
> 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
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

> where freetext (document, @.searchterm) and
> dbo.tblDocument.SubCategoryID = @.subcategory AND
> dbo.tblDocument.CategoryID = @.category AND
> dbo.tblDocument.SubmitterID = @.submitter
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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Oh yes! I remember that trick of setting the variables to null now...
Thanks!!
"Jeff" wrote:

> (DML below)
> First, thank you in advance for your help. Here's one for those of you th
at
> like a little challenge, or perhaps this is a cake walk for you (I'm jealo
us).
> 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 fo
r
> this job to create a search engine like stored procedure.(if there's a bet
ter
> 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 search
es
> 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 SUBcategor
ies.
> 5) Dropdown box: Ability to choose whom submitted the document. If
> SubmitterName is choosen, the stored procedure will only return thos docum
ent
> OR systems meeting that criteria. If left blank will search all systems A
ND
> 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, FILEGRO
WTH
> = 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]
> )|||Jeff,
> But where I'm really at a loss is the stored procedures themselves...
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
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:BDC416B4-331A-46C4-A6C3-85F9844DC90C@.microsoft.com...
> Oh yes! I remember that trick of setting the variables to null now...
> Thanks!!
> "Jeff" wrote:
>|||On Sun, 11 Sep 2005 11:22:04 -0700, Jeff wrote:
(snip)
>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
(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
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> This DB is to hold information so that our helpdesk can search it for sup
port information. <<
Have you looked into a Help Desk package instead of writing your own?