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