Как строить поисковые системы, работающие через WEB
|
marginwidth="0">
Листинг 1. Выбор типа поиска
IF @strSearchType = `Author`
Begin
Select From Where
End
ELSE IF @strSearchType = `Title`
Begin
Select From Where
End
ELSE IF @strSearchType = `Author_ID`
Begin
Selec From Where
End
ELSE
SELECT `Invalid search type`
Листинг 2. Подсоединение к базе данных и запуск процедуры
‘Открыть соединение и получить набор записей
Set objADOConn = Server.CreateObject("ADODB.Connection")
objADOConn.Open "dsn=webPUBS;uid=sa;pwd="
Set objRst = Server.CreateObject("ADODB.Recordset")
`Получить набор записей через хранимую процедуру
strSQL = "EXEC websp_PUBS `" + strSelectType + "`, `" + strUSER_INPUT
strSQL = strSQL + "`, `" + strYTDsales + "` "
objRst.Source = strSQL
Set objRst.ActiveConnection = objADOConn
objRst.Open
if exists (select * from sysobjects where id = object_id(`dbo.websp_Pubs`) and sysstat & 0xf = 4)
drop procedure dbo.websp_Pubs
GO
CREATE proc dbo.websp_Pubs
(
@strSearchType varchar(255) = "AUTHOR",
@strUSERINPUT varchar(255) = "ALL" ,
@strYTDsales varchar(255) = "All"
)
AS
SET NOCOUNT ON
SET ROWCOUNT 100
/* PURPOSE: DataWarehouse Lookup Project for SQLServer Magazine
-------------------------------------------------------------- */
declare @NameSrch varchar(255)
declare @moneyYTDsales money
declare @TitleSrchcnt integer
/* Trim off the excess spaces before doing the search */
SELECT @strSearchType = RTRIM(LTRIM(@strSearchType))
SELECT @strUSERINPUT = RTRIM(LTRIM(@strUSERINPUT))
/* --------------------------------------------------------
Validate and Convert YTD sales parameter to Money
- incoming sales parameter must be converted for SARG usage
-------------------------------------------------------- */
SELECT @moneyYTDsales = CASE WHEN @strYTDsales LIKE `%[^0-9]%` Then 0
ELSE CONVERT(money,@strYTDsales) END
/* --------------------------------------------------------
AUTHOR: Get a list of Author information
-------------------------------------------------------- */
IF @strSearchType = "AUTHOR"
BEGIN
SELECT @NameSrch = @strUSERINPUT + `%`
SELECT
Author = SUBSTRING("<A HREF=`pubslookup.asp?Author_id=" + COALESCE(A.au_id,``) + "`>" + SUBSTRING(COALESCE((au_fname + ` ` + au_lname),`-`),1,25) + "</A>" , 1,75) ,
/* final substring lenght >= total characters added up: hidden HTML + char showing + plus extra spaces if desired */
Title = COALESCE(SUBSTRING(Title,1,65),`-`),
Type = COALESCE(T.type,`-`),
Price = `$` + COALESCE(CONVERT(VARCHAR(7),T.price),`-`),
Sales = `$` + COALESCE(CONVERT(VARCHAR(12),T.ytd_sales),`-`)
From authors AS A
inner join titleauthor AS TA ON
A.au_id = TA.au_id
inner join titles AS T ON
TA.title_id = T.title_id
Where A.au_lname like @NameSrch
and T.ytd_sales >= @moneyYTDsales
ORDER BY T.TITLE, A.AUTHOR
END
/* --------------------------------------------------------
TITLE: Get a list of Titles
-------------------------------------------------------- */
ELSE IF @strSearchType = "TITLE"
BEGIN
SELECT @NameSrch = @strUSERINPUT + `%`
/* find out how many will be in search */
SELECT @TitleSrchcnt = (Select count(*)
FROM titles
WHERE Title like @NameSrch
AND ytd_sales >= @moneyYTDsales)
IF @TitleSrchcnt > 1
SELECT
Title = SUBSTRING(COALESCE(T.Title,`-`),1,35),
Author = SUBSTRING("<A HREF=`pubslookup.asp?Author_id=" + COALESCE(A.au_id,``) + "`>" + SUBSTRING(COALESCE((au_fname + ` ` + au_lname),`-`),1,25) + "</A>" , 1,75) ,
/* final substring lenght >= total characters added up: hidden HTML + char showing + plus extra spaces if desired */
Publisher = SUBSTRING(COALESCE((P.pub_name),`-`),1,25)
From titles AS T
inner join titleauthor AS TA ON
TA.title_id = T.title_id
left outer join authors AS A ON
A.au_id = TA.au_id
left outer join publishers AS P ON
T.pub_id = P.pub_id
Where T.Title like @NameSrch
and T.ytd_sales >= @moneyYTDsales
ORDER BY T.TITLE, A.AUTHOR
ELSE IF @TitleSrchcnt <= 1 /* we have one, or none:
we include the 0 none case so an empty record set is returned,
the ASP page handles this case. If no record set is returned an error occurs */
SELECT
Title = SUBSTRING(COALESCE(T.Title,`-`),1,35),
Type = COALESCE(T.type,`-`),
Price = `$` + COALESCE(CONVERT(VARCHAR(12),T.price),`-`),
Advance = `$` + COALESCE(CONVERT(VARCHAR(12),T.advance),`-`),
Sales = `$` + COALESCE(CONVERT(VARCHAR(12),T.ytd_sales),`-`),
Author = SUBSTRING("<A HREF=`pubslookup.asp?Author_id=" + COALESCE(A.au_id,``) + "`>" + SUBSTRING(COALESCE((au_fname + ` ` + au_lname),`-`),1,25) + "</A>" , 1,75) ,
/* final substring lenght >= total characters added up: hidden HTML + char showing + plus extra spaces if desired */</P>
Publisher = SUBSTRING(COALESCE((P.pub_name),`-`),1,25),
City = SUBSTRING(COALESCE((P.city),`-`),1,25),
State = COALESCE(P.State,`-`)
From titles AS T
inner join titleauthor AS TA ON
TA.title_id = T.title_id
left outer join authors AS A ON
A.au_id = TA.au_id
left outer join publishers AS P ON
T.pub_id = P.pub_id
Where T.Title like @NameSrch
and T.ytd_sales >= @moneyYTDsales
END /* Title search */
ELSE IF @strSearchType = "Author_ID"
BEGIN
SELECT
Author_ID = A.au_id,
Author = SUBSTRING(COALESCE((A.au_fname + ` ` + A.au_lname),`-`),1,75),
Address = SUBSTRING(COALESCE((A.address),`-`),1,75),
City = SUBSTRING(COALESCE((A.city),`-`),1,25),
State = COALESCE(A.State,`-`),
Zip = SUBSTRING(COALESCE((A.Zip),`-`),1,5),
"# Books Written" = CONVERT(varchar(10),(select count(*) from titleauthor AS TA where TA.au_id = A.au_id))
From authors AS A
Where A.au_id = @strUSERINPUT
END
ELSE
SELECT `Invalid search type`
SET ROWCOUNT 0
GO
GRANT EXECUTE ON dbo.websp_Pubs TO guest
GO
