When programming in SQL, you have probably created
various types of SQL statements to execute many common actions. You use
SQL statements to create databases, create tables, modify tables, or
perform data entry. These actions are performed by you the programmer as
you need them. In SQL, you can create a statement to be treated as an
object and use it only when needed. You can create the statement as an
object called a stored procedure.
Learning: Creating the Database
- Start Microsoft SQL Server Management Studio and click Connect to connect
to the database server
- To create a database, right-click the Databases node and click New
- Set the Database Name to SuperMarket and click OK
- Expand the SuperMarket node and the Tables node
- Right-click Tables and click click New Table...
- Set the name of the first column to EmployeeID and its data Type to
- In the lower section of the window, expand the Identity Specification and
set the (Is Identity) to Yes
- Right-click EmployeeID and click Set Primary Key
- Create the other columns as follows:
- Close the table
- When asked whether you want to save, click Yes
- Set the table name to Employees and press Enter
- Under the Tables node of the SuperMarket database, right-click
dbo.Employees and click Open Table
- Fill it up with a few records as follows:
- Close the table
A stored procedure is a SQL statement created and
saved in a database as an object. Before creating a stored procedure, you
must identify the desired database, expand its Programmability node,
right-click its Stored Procedures node, and click New Stored Procedure...
The code of a stored procedure primarily that of a regular SQL statement.
After creating the SQL statement, you must execute it. You can do this by
clicking the Execute button. Once the stored procedure has been executed,
its name is added as a new item to the Stored Procedures node of the
database. This indicates that it is ready to be used.