How to Create a Searchable Web Database

A database holds a list of records in objects called tables. These tables are searchable from your Web applications. A database and the tables it contains are created using the Structured Query Language (SQL). Additionally, the records contained within the tables are also created and retrieved using SQL. You create a searchable Web database by creating a new database and tables that hold records such as customers, sales and product information.

Things You'll Need

• Microsoft SQL Management Studio
• SQL web host account

Instructions

1. Click the Windows "Start" button and click "Programs." Click "Microsoft SQL Server" and then click "SQL Server Management Studio." A prompt opens where you enter the location of your online SQL Server host. Enter the SQL address and password into the text boxes and click "Connect." Click the "New Query" button to open a text editor. This is where you enter your SQL code to create the searchable database.

2. Create your database. The database contains all the user permissions, tables, views and stored procedures that make your Web database searchable. The following SQL code creates the database:

   CREATE DATABASE my_search_db

   Press the F5 key to execute the code. This creates a database on your SQL Server.

3. Create the tables used for searches. Each table in your database can be used in a search. You allow users to search and read the data using your own code and database-stored procedures. In this instance, a customer table is created. The following code creates a table:

   CREATE TABLE customer

   (

   id int,

   LastName varchar(255),

   FirstName varchar(255),

   )

   Press the F5 key to execute this code. This simple table creates a list of customers. The "id" field is used as a field that gives the customer a unique identification number. This is also used for internal employees when they want to search for a customer ID number.

4. Insert some data into your table. A successful search requires some data in the tables. Insert some data into your customer table to test out your new database. The following code inserts a customer record into the table:

   insert into customer (1, 'Joe', 'Smith')

5. Create a stored procedure. A stored procedure is a reusable block of code that executes SQL commands. A stored procedure can be used to create a simple search for a customer. The following code creates a stored procedure to search your Web database for a customer:

   create procedure sel_customer

   ( @id int )

   as

   select * from customer where id=@id

   This stored procedure retrieves a customer based on the customer ID you send it. The @id variable is used to locate the customer and send it to the requesting application.

6. Execute the stored procedure to test your search database. The following code searches the database and shows you the "Joe Smith" customer entered into the customer table:

   exec sel_customer 1

References

• W3 Schools: SQL Create Database Statement
• 1Key Data: Create Table Statement
• W3 Schools: SQL Select Statement