Step 1. SQL Server table and stored procedure
First we create a table called "quotes". Open SQL Server and open a new query window in your database. Cut and
paste the following SQL statement below to create a table called "quotes". This table will store our data about the famous computer quotes
that we want to display.
CREATE TABLE [dbo].[Quotes](
[QID] [int] IDENTITY(1,1) NOT NULL,
[quote] [varchar](500) NOT NULL,
[author] [varchar](50) NULL,
CONSTRAINT [PK_Quotes] PRIMARY KEY CLUSTERED
(
[QID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
|
You can use Google to search for famous quotes. Manually enter the author and what they said into the table.
After adding some data your table should look similar to this with different quotes and authors naturally.
Next keeping an eye on security and performance we'll create a SQL Server stored procedure called sp_dailyquotes_select to query the
quotes from the table we just created. Be sure to assign execute permissions for this stored procedure to the sql login that
your web site uses to query the database.
CREATE PROCEDURE [dbo].[sp_dailyquotes_select]
AS
select top 1 quote, author from quotes order by newid()
|
By selecting top 1 and using newid() we'll get 1 random quote every time its run.
Being good programmers we always test our code on the database before hooking it into a web page so manually execute the store procedure we just created in a query window

Next Lesson
In Step 2. ASP.NET User Control we'll setup an ASP.NET page using an ASP.NET User Control to display the results of our computer quotes query.
|