PageText Stored Procedure in Visual Studio .NET

Make Denso QR Bar Code in Visual Studio .NET PageText Stored Procedure

The GetPageText stored procedure is called from the Book Contents page It returns the text for the page, as well as the different title text CREATE PROCEDURE GetPageText @SectionID integer AS Declare @BookPage varchar(8000), @SectionName varchar(50), @ID integer, @Name varchar(50), @BookID integer, @BookName varchar(50) BEGIN Select @BookPage = BookPage, @SectionName = SectionName, @ID = ID from Sections Where SectionID = @SectionID Select @Name = Name, @BookID = BookID From s Where ID = @ID Select @BookName = BookName From Books Where BookID = @BookID Select @BookPage as BookPage, @SectionName as SectionName, @Name as Name, @BookName as BookName END GO

Brought to you by ownSky! 474

The procedure expects one parameter, the ID of the section that the content is to be retrieved for: @SectionID integer Locally, you declare a variable to store that text: @BookPage varchar(8000), as well as the name of the section: @SectionName varchar(50), the ID of the chapter that the section is in: @ID integer, the name of the chapter that the section is in: @Name varchar(50), the ID of the book that the chapter is in: @BookID integer, and also the name of that book @BookName varchar(50) First, you need to retrieve the section information according to the ID of the section passed in: Select @BookPage = BookPage, @SectionName = SectionName, @ID = ID from Sections Where SectionID = @SectionID You can then retrieve the chapter name: Select @Name = Name, @BookID = BookID From s Where ID = @ID and finally the name of the book: Select @BookName = BookName From Books Where BookID = @BookID The text of the section, as well as the different title information for the section, is returned from the procedure: Select @BookPage as BookPage, @SectionName as SectionName, @Name as Name, @BookName as BookName

The GetGlossaryTerm stored procedure returns the text of a glossary term The procedure is called from the Glossary page CREATE PROCEDURE GetGlossaryTerm @GlossaryID integer AS BEGIN Select GlossaryTerm, TermDefinition From GlossaryTerms Where GlossaryID = @GlossaryID END GO Passed into the procedure is the ID of the glossary term: @GlossaryID integer That ID is then used to return the definition to the calling application:

Brought to you by ownSky! 475

The SearchSectionList stored procedure returns all the sections that match the search text passed in The procedure is called from the Search Results page CREATE PROCEDURE SearchSectionList @SearchTerm varchar(102) AS Declare @AllOfIt varchar(8000), @SectionID varchar(10), @SectionName varchar(50) Select @SearchTerm = '%' + @SearchTerm + '%' Declare CurSections Cursor For Select SectionID, SectionName from Sections Where SectionName Like @SearchTerm Or BookPage Like @SearchTerm order by SequenceNumber Open CurSections Fetch CurSections Into @SectionID, @SectionName Select @AllOfIt = '' While @@Fetch_Status = 0 BEGIN Select @AllOfIt = @AllOfIt + '<TR><TD WIDTH=528>' + '<P><A HREF="/book_pageasp SectionID=' + @SectionID + '">' + @SectionName + '</A></P></TD></TR>' Fetch CurSections Into @SectionID, @SectionName END Close CurSections Deallocate CurSections Select @AllOfIt as TheTable GO Passed into the procedure is the search text: @SearchTerm varchar(102) Within the procedure, you declare a variable that will contain the matching sections as HTML Row tags: @AllOfIt varchar(8000), You also declare variables that are used to retrieve data from the Sections table: @SectionID varchar(10), @SectionName varchar(50) For a section record to match the search text, it can contain the search text anywhere in the name or text of the section Therefore, you need to surround the search text with wildcard characters: Select @SearchTerm = '%' + @SearchTerm + '%' Next you declare a cursor object: Declare CurSections Cursor that retrieves the sections that have the search text in the name of the section or in the content of the section: For Select SectionID, SectionName

Brought to you by ownSky! 476

from Sections Where SectionName Like @SearchTerm Or BookPage Like @SearchTerm order by SequenceNumber You then open the cursor: Open CurSections and retrieve the first record: Fetch CurSections Into @SectionID, @SectionName You need to initialize your return variable: Select @AllOfIt = '' and enter a loop so that you can process each matching section: While @@Fetch_Status = 0 Within the loop, each matching record is returned as an HTML Row tag: Select @AllOfIt = @AllOfIt + '<TR><TD WIDTH=528>' + '<P><A HREF="/book_pageasp SectionID=' + @SectionID + '">' + @SectionName + '</A></P></TD></TR>' You then retrieve the next record: Fetch CurSections Into @SectionID, @SectionName After the loop, the cursor is released: Close CurSections Deallocate CurSections and you return the matching records as rows from an HTML Table: Select @AllOfIt as TheTable