Fundamentals of Stored Procedures

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

 

Stored Procedures

 

Fundamentals of Stored Procedures

 

Introduction 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.

 

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

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