Fundamentals of Stored Procedures

http://www.functionx.com/sqlserver/Lesson17.htm

 

Stored Procedures

 

Fundamentals of Stored Procedures

In Lesson 6, we had an introduction to some types of actions that could be performed on a database. These actions were called functions. The SQL provides another type of action called a stored procedure. If you have developed applications in some other languages such as Pascal or Visual Basic, you are probably familiar with the idea of a procedure. Like a function, a stored procedure is used to perform an action on a database.

Introduction

Practical Learning: Introducing Stored Procedures

1. Start Microsoft SQL Server Management Studio and log in to your server
2. On the main menu, click File -> New -> Query With Current Connection
3. To create a new database, enter the following code in the window

-- =============================================
-- Database: WattsALoan
-- =============================================
USE master
GO
-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'WattsALoan'
)
DROP DATABASE WattsALoan
GO

CREATE DATABASE WattsALoan
GO
-- =========================================
-- Table: Employees
-- =========================================
USE WattsALoan
GO

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
  DROP TABLE dbo.Employees
GO

CREATE TABLE dbo.Employees
(
    EmployeeID int identity(1,1) NOT NULL,
    EmployeeNumber nchar(10) NULL,
    FirstName varchar(20) NULL,
    LastName varchar(10),
    FullName AS ((LastName+ ', ') + FirstName),
    Title varchar(100),
    HourlySalary money,
    Username varchar(20),
    Password varchar(20),
    CONSTRAINT PK_Employees PRIMARY KEY(EmployeeID)
)
GO
INSERT INTO dbo.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES('293747', 'Jeanne', 'Tryler', 'Accounts Manager', 22.24);
GO
INSERT INTO dbo.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES('492947', 'Helene', 'Gustman', 'Accounts Representative', 14.55);
GO
INSERT INTO dbo.Employees(EmployeeNumber, FirstName, LastName, Title, HourlySalary)
VALUES('804685', 'Ernest', 'Thomas', 'Accounts Representative', 12.75);
GO
-- =========================================
-- Table: LoanTypes
-- =========================================
USE WattsALoan
GO

IF OBJECT_ID('dbo.LoanTypes', 'U') IS NOT NULL
  DROP TABLE dbo.LoanTypes
GO

CREATE TABLE dbo.LoanTypes
(
    LoanTypeID int identity(1,1) NOT NULL, 
    LoanType varchar(50) NOT NULL, 
    CONSTRAINT PK_LoanTypes PRIMARY KEY(LoanTypeID)
);
GO
INSERT INTO LoanTypes(LoanType) VALUES('Personal Loan');
GO
INSERT INTO LoanTypes(LoanType) VALUES('Car Financing');
GO
INSERT INTO LoanTypes(LoanType) VALUES('Credit Card');
GO
INSERT INTO LoanTypes(LoanType) VALUES('Furniture Loan');
GO
-- =========================================
-- Table: Customers
-- =========================================
USE WattsALoan
GO

IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL
  DROP TABLE dbo.Customers
GO

CREATE TABLE dbo.Customers
(
    CustomerID int identity(1,1) NOT NULL,
    DateCreated datetime NULL,
    FullName varchar(50) NOT NULL,
    BillingAddress varchar(100),
    BillingCity varchar(50),
    BillingState varchar(50),
    BillingZIPCide varchar(10),
    EmailAddress varchar(100),
    CONSTRAINT PK_Customers PRIMARY KEY(CustomerID)
)
GO
INSERT INTO Customers(DateCreated, FullName,
    BillingAddress, BillingCity, BillingState,
    BillingZIPCide, EmailAddress)
VALUES('2/26/2004', 'Julius Ramse',
       '927 Feuler Ave', 'Silver Spring', 
       'MD', '20904', '[e-mail]');
GO
INSERT INTO Customers(DateCreated, FullName,
    BillingAddress, BillingCity, BillingState,
	BillingZIPCide)
VALUES('06/22/2006', 'Gertrude Vaillant',
       '10055 Larsenic Rd', 'Takoma Park', 
       'MD', '20910');
GO
INSERT INTO Customers(DateCreated, FullName,
    BillingAddress,	BillingCity, BillingState,
	BillingZIPCide, EmailAddress)
VALUES('12/3/2004', 'James Barrouch',
       '4204 Fallon Drive', 'Silver Spring',
       'MD', '20906', '[e-mail]');
GO
INSERT INTO Customers(DateCreated, FullName,
    BillingAddress,	BillingCity, BillingState,
	BillingZIPCide)
VALUES('08/02/2006', 'Christine Rougher',
       '825 Manning Street', 'Alexandria', 
       'VA', '22231');
GO
INSERT INTO Customers(DateCreated, FullName,
    BillingAddress,	BillingCity, BillingState,
	BillingZIPCide, EmailAddress)
VALUES('10/08/2006', 'Patrick Heller',
       '2480 Clarington Drive NW', 'Washington', 
       'DC', '20006', '[e-mail]');
GO
-- =========================================
-- Table: LoanAllocation
-- =========================================
USE WattsALoan
GO

IF OBJECT_ID('dbo.LoanAllocations', 'U') IS NOT NULL
  DROP TABLE dbo.LoanAllocations
GO

CREATE TABLE dbo.LoanAllocations
(
    LoanAllocationID int identity(1,1) NOT NULL,
    DatePrepared datetime NOT NULL, 
    EmployeeID int NULL
		CONSTRAINT FK_LoanPreparer
		FOREIGN KEY REFERENCES Employees(EmployeeID),
    CustomerID int NOT NULL
		CONSTRAINT FK_LoanReceiver
		FOREIGN KEY REFERENCES Customers(CustomerID),
    AccountNumber char(10),
    LoanTypeID int NOT NULL
		CONSTRAINT FK_LoanTypes
		FOREIGN KEY REFERENCES LoanTypes(LoanTypeID),
    LoanAmount money NOT NULL,
    InterestRate decimal(6,2) NOT NULL,
    Periods decimal(6,2) NOT NULL,
    InterestAmount AS ((LoanAmount*(InterestRate/(100)))*(Periods/(12))),
    FutureValue AS (LoanAmount+(LoanAmount*(InterestRate/(100)))*(Periods/(12))),
    MonthlyPayment AS ((LoanAmount+(LoanAmount*(InterestRate/(100)))*(Periods/(12)))/Periods),
    Notes Text,
    CONSTRAINT PK_LoanAllocations PRIMARY KEY(LoanAllocationID)
)
GO
INSERT INTO LoanAllocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES('2/26/2004', 2, 1, '9171394', 4, 6500.00, 12.65, 36, 
       'The loan will be delivered by our furniture business partner Helios Furnian');
GO
INSERT INTO LoanAllocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES('06/22/2007', 2, 2, '8628064', 2, 16500.00, 10.20, 60, 
       'For this car loan, our partner Arlington Honda will process and deliver the car.');
GO
INSERT INTO LoanAllocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES('12/3/2006', 1, 3, '8468364', 3, 500.00, 18.65, 48, 
       'This is a regular credit card.');
GO
INSERT INTO LoanAllocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES('08/02/2006', 3, 4, '2483047', 1, 3500.00, 12.74, 36, 
       'This is personal/cash loan allocated to a customer who walked in the store and requested it.');
GO
INSERT INTO LoanAllocations(DatePrepared, EmployeeID,
	CustomerID, AccountNumber, LoanTypeID, LoanAmount,
    InterestRate, Periods, Notes)
VALUES('10/08/2006', 2, 5, '1311804', 4, 22748.36, 12.28, 60, 
       'This is a regular car financing loan');
GO
-- =========================================
-- Table: Payments
-- =========================================
USE WattsALoan
GO

IF OBJECT_ID('dbo.Payments', 'U') IS NOT NULL
  DROP TABLE dbo.Payments
GO

CREATE TABLE dbo.Payments
(
    PaymentID int identity(1, 1) NOT NULL,
    PaymentDate datetime NOT NULL, 
    EmployeeID int NULL
		CONSTRAINT FK_Employees
		FOREIGN KEY REFERENCES Employees(EmployeeID),
    LoanAllocationID int NOT NULL
		CONSTRAINT FK_LoanAllocations
		FOREIGN KEY REFERENCES LoanAllocations(LoanAllocationID),
    PaymentAmount money NOT NULL,
    Balance money,
    Notes Text,
    CONSTRAINT PK_Payments PRIMARY KEY(PaymentID)
)
GO

1. To execute the code, press F5
2. In the Object Explorer, expand the Databases node if necessary and expand WattsALoan
3. Click Database Diagram
4. When the message box comes up, read it and click Yes
5. Right-click Database Diagram and click New Database Diagram...
6. In the dialog box, double-click each table and, when all tables have been added, click Close
   
7. Save the diagram as dgmWattsALoan and close it