Example Application: Elementary Addition

	Stored Procedures

Fundamentals of Stored Procedures

Introduction

Besides functions, Transact-SQL provides another type of action called a stored procedure. A stored procedure is used to perform a DML (data manipulation language) action such as adding, editing, or removing records. This means that a stored procedure can include one or more SQL statements. In fact, unlike a DML statement you must pass to a command, a stored procedure simplifies the ability to perform an action.

Practical Learning: Introducing Stored Procedures

Start Microsoft Visual Studio
To create a new application, on the main menu, click FILE -> New -> Project...
In the middle list, click Windows Forms Application
Change the Name to WattsALoan2 and click OK
In the Server Explorer, right-click any connection and click New Query
Open the Watts A Loan1 file and select everything in it the document
Copy and paste it in the Query window
Right-click inside the document and click Execute
In the Server Explorer, expand the WattsALoan1 connection
In the Solution Explorer, right-click Form1.cs and click Rename
Type WattsALoan.cs and press Enter

Creating a Stored Procedure

To create a stored procedure, in Microsoft Visual Studio, in the Server Explorer, under the database connection, expand the database that will own the procedure, right-click Stored Procedures and click Add New Stored Procedure. An empty window would open in the Code Editor, waiting for you to do your thing

CREATE PROCEDURE [dbo].[Procedure]
	@param1 int = 0,
	@param2 int
AS
	SELECT @param1, @param2
RETURN 0

To create a procedure in Transact-SQL, start with the CREATE PROCEDURE expression. You can also use CREATE PROC. Like everything in your database, you must name your procedure:

The name of a procedure can be any string that follows the rules for naming the functions
Refrain from starting the name of a procedure with sp_ because it may conflict with some of the stored procedures that already ship with Microsoft SQL Server

After the name of the procedure, type the keyword AS. The section, group of words, or group of lines after the AS keyword is called the body of the stored procedure. It states what you want the procedure to do or what you want it to produce. Based on this, the simplest syntax to create a stored procedure is:

CREATE PROCEDURE [SchemaName.]ProcedureName
AS
Body of the Procedure

You can also start the body of the stored procedure with BEGIN and end it with END. The formula to use would be:

CREATE PROCEDURE [SchemaName.]ProcedureName
AS
BEGIN
	Body of the Procedure
END

It is important to keep in mind that there are many other issues related to creating a stored procedure but for now, we will consider that syntax. Probably the simplest procedure you can create would consist of selecting fields from a table or a view. To do this, after the AS operator, enter a SELECT expression and apply the techniques we reviewed for data analysis. For example, to create a stored procedure that would hold a list of students from a table named Students, you would create the procedure as follows:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Stored Procedures";
        Load += new EventHandler(FormLoaded);
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE PROCEDURE Personnel.GetEmployees " +
                               "AS " +
                               "    SELECT FirstName, " +
                               "    	   LastName, " +
                               "    	   HourlySalary " +
                               "    FROM Employees;",
                cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();

            MessageBox.Show("The stored procedure has been created.",
                            "Exercise",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Besides SELECT operations, in a stored procedure, you can perform any of the database operations we have applied so far. These include creating and maintaining records, etc.

Setting No Count

When a stored procedure produces its results (that is, when it executes), the database engine must keep sending messages back and forth between the server and the client. An example of those messages is that every time the stored procedure produces its result(s), the server must specify the number of records. These relentless interactions may create too much processing and in most cases are not necessary. To avoid them, after the AS keyword, add a SET NOCOUNT ON expression before starting the body of the stored procedure. The formula to follow is:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
SET NOCOUNT ON;
Body of the Procedure

Here is an example:

CREATE PROCEDURE Registration.GetIdentification
AS
SET NOCOUNT ON;
BEGIN
    SELECT FirstName,
	   LastName,
	   DateOfBirth,
	   Gender
    FROM Registration.Students;
END
GO

Although you can create and use a stored procedure without specifying a schema, it is recommended that you always use a schema. In fact, you should always create your own schema in your database and create your stored procedure in it.

Practical Learning: Creating Stored Procedures

Display the WattsALoan form and double-click an unoccupied area of its body

Create a new stored procedure as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WattsALoan2
{
    public partial class WattsALoan : Form
    {
        public WattsALoan()
        {
            InitializeComponent();
        }

        void CreateStoredProcedure()
        {
            using (SqlConnection cntWattsALoan =
            new SqlConnection("Data Source=(local); " +
                      "Database='WattsALoan1'; " +
                      "Integrated Security='SSPI';"))
            {
                SqlCommand cmdWattsALoan =
                    new SqlCommand("CREATE PROCEDURE Personnel.GetEmployees " +
	                          "AS " +
		                  "	SET NOCOUNT ON; " +
				  "	SELECT EmployeeNumber, " +
                        	  "	       FullName, " +
	                          "	       Title, " +
			          "	       WorkPhone, " +
                	          "	       [Address], " +
				  "	       City, " +
		                  "	       [State], " +
                	          "	       ZIPCode " +
		                  "	FROM   Personnel.Employees;",
		                  cntWattsALoan);

                cntWattsALoan.Open();
                cmdWattsALoan.ExecuteNonQuery();
                MessageBox.Show("A new stored procedure has been created.",
                                "Watts a Loan",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void WattsALoan_Load(object sender, EventArgs e)
        {
            CreateStoredProcedure();
        }
    }
}

Execute the application

Click OK on the message box

Close the form and return to your programming environment
To create a stored procedure, in the Server Explorer and under the WattsALoan1 node, right-click Stored Procedures and click Add New Stored Procedure

Change the code as follows:

CREATE PROCEDURE Accounts.GetCustomers
AS
    SET NOCOUNT ON;
    SELECT custs.AccountNumber,
	   custs.AccountDate,
	   custs.FirstName,
	   custs.MiddleName,
	   custs.LastName,
	   custs.WorkPhone,
	   custs.HomePhone,
	   custs.[Address],
	   custs.City,
	   custs.[State],
	   custs.ZIPCode,
	   custs.EmailAddress
    FROM   Accounts.Customers custs;
RETURN 0

To add the stored procedure to the database, under the tab, click the Update button
A dialog box titled Preview Database Updates appears. After a few seconds, a few lines of text appear:

In the bottom section of the Preview Database Updates dialog box, click Update Database
Click inside the window, press Ctrl + A to select everything

To create a stored procedure that gets its records from a view, type the following:

CREATE PROCEDURE Loans.GetAllocations
AS
    SET NOCOUNT ON;
    SELECT sa.[Loan #],
           sa.[Date Prepared],
           sa.[Processed By],
	   sa.[Processed For],
           sa.[Customer Name],
           sa.[Loan Type],
	   sa.Amount,
           sa.[Interest Rate],
	   sa.[Loan Length],
           sa.[Interest Amount],
	   sa.[Future Value],
           sa.[Monthly Payment],
           sa.Notes
    FROM   Loans.ShowAllocations sa;
GO

To add the stored procedure to the database, right-click inside the window and click Execute
Click inside the window and press Ctrl + A

To create another stored procedure, type the following code:

CREATE PROCEDURE Loans.GetPayments
AS
    SET NOCOUNT ON;
    SELECT ALL * FROM Loans.ShowPayments;
GO

To add the stored procedure to the database, under the tab, click the Update button
In the Preview Database Updates dialog box, click Update Database

Executing a Stored Procedure

To get the results of a stored procedure, you must execute it (in other words, to use a stored procedure, you must call it). To execute a stored procedure in Microsoft Visual Studio:

If the stored procedure is opened in the Code Editor, you can right-click anywhere in the window and click Execute
In the Server Explorer (even if the stored procedure is displaying its text in the Code Editor), right-click the name of the stored procedure and click Execute

To execute a procedure in a Windows Forms application, you have two options. You can use the EXECUTE command followed by the name of the procedure. Although there are some other issues related to executing a procedure, for now, we will consider that the simplest syntax to call a procedure is:

EXECUTE [SchemaName.]ProcedureName

Instead of EXECUTE, you can use the EXEC keyword:

EXEC [SchemaName.]ProcedureName

As an example, if you have a procedure named GetStudentIdentification, to execute it, you would type:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    ListView lvwEmployees;
    ColumnHeader colFirstName;
    ColumnHeader colLastName;
    ColumnHeader colHourlySalary;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        colFirstName = new ColumnHeader();
        colFirstName.Width = 80;
        colFirstName.Text = "First Name";

        colLastName = new ColumnHeader();
        colLastName.Text = "Last Name";
        colLastName.Width = 80;
        
        colHourlySalary = new ColumnHeader();
        colHourlySalary.Text = "Salary";
        colHourlySalary.TextAlign = System.Windows.Forms.HorizontalAlignment.Right;

        lvwEmployees = new ListView();
        lvwEmployees.GridLines = true;
        lvwEmployees.FullRowSelect = true;
        lvwEmployees.Location = new Point(12, 12);
        lvwEmployees.View = System.Windows.Forms.View.Details;
        lvwEmployees.Size = new System.Drawing.Size(244, 164);
        lvwEmployees.Columns.Add(colFirstName);
        lvwEmployees.Columns.Add(colLastName);
        lvwEmployees.Columns.Add(colHourlySalary);

        Controls.Add(lvwEmployees);
        Text = "Stored Procedures";
        Controls.Add(this.lvwEmployees);
        ClientSize = new System.Drawing.Size(266, 188);
        StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
        Load += new EventHandler(FormLoaded);
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise = new SqlCommand("EXECUTE Personnel.GetEmployees;",
                cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();

            SqlDataAdapter sdaExercise = new SqlDataAdapter(cmdExercise);
            DataSet dsExercise = new DataSet("ExerciseSet");

            sdaExercise.Fill(dsExercise);

            foreach (DataRow person in dsExercise.Tables[0].Rows)
            {
                ListViewItem lviPerson = new ListViewItem(person["FirstName"].ToString());
                lviPerson.SubItems.Add(person["LastName"].ToString());
                lviPerson.SubItems.Add(person["HourlySalary"].ToString());
                lvwEmployees.Items.Add(lviPerson);
            }
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Another option to execute a stored procedure in a Windows Forms Application is to simply pass its name to the command.

Practical Learning: Executing Stored Procedures

To create a new form, in the Solution Explorer, right-click WattsALoan2 -> Add -> Windows Form...
Set the Name to Employees
Click Add

Design the form as follows:

Control	(Name)	Anchor	Text
DataGridView	dgvEmployees	Top, Bottom, Left, Right
Button	btnClose	Bottom, Right	Close

Double-click an unoccupied area of the form
Return to the form and double-click the Close button

change the document as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WattsALoan2
{
    public partial class Employees : Form
    {
        public Employees()
        {
            InitializeComponent();
        }

        private void Employees_Load(object sender, EventArgs e)
        {
            SqlDataAdapter sdaEmployees = new SqlDataAdapter();
            
            using (SqlConnection cnnWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                   "Database='WattsALoan1';" +
                                   "Integrated Security='SSPI';"))
            {
     SqlCommand cmdEmployees = new SqlCommand("EXECUTE Personnel.GetEmployees;",
                                    cnnWattsALoan);

                cnnWattsALoan.Open();

                DataSet dsEmployees = new DataSet("EmployeesSet");

                sdaEmployees.SelectCommand = cmdEmployees;
                sdaEmployees.Fill(dsEmployees);
                dgvEmployees.DataSource = dsEmployees.Tables[0];
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

To create a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to Customers
Click Add

Design the form as follows:

Control	(Name)	Anchor	Text
DataGridView	dgvCustomers	Top, Bottom, Left, Right
Button	btnClose	Bottom, Right	Close

Double-click an unoccupied area of the form
Return to the form and double-click the Close button
change the document

Implement its even as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WattsALoan2
{
    public partial class Customers : Form
    {
        public Customers()
        {
            InitializeComponent();
        }

        private void Customers_Load(object sender, EventArgs e)
        {
            DataSet dsCustomers = new DataSet("CustomersSet");

            using (SqlConnection scWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                   "Database='WattsALoan1';" +
                                   "Integrated Security=yes;"))
            {
                SqlDataAdapter sdaCustomers =
                        new SqlDataAdapter("Accounts.GetCustomers",
                                           scWattsALoan);


                scWattsALoan.Open();
                sdaCustomers.Fill(dsCustomers);
                dgvCustomers.DataSource = dsCustomers.Tables[0];
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

To create a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to NewLoanAllocation
Click Add

Design the form as follows:

Control	Text	Name	Other Properties
Label	Date Prepared:
DateTimePicker		dtpDateAllocated
Label	Prepared By		BackColor: Gray AutoSize: False
Label	Employee #:
MaskedTextBox		txtEmployeeNumber	Mask: 00-000
TextBox		txtEmployeeName
Label	Prepared For		BackColor: Gray AutoSize: False
Label	Account #:
MaskedTextBox		txtAccountNumber	Mask: 0000-0000
TextBox		txtCustomerName
Label	Loan Preparation		BackColor: Gray AutoSize: False
Label	Loan Type:
ComboBox		cbxLoanTypes
Button	New Type of Loan	btnLoansTypes
Label	Loan Amount:
TextBox	0.00	txtPresentValue	TextAlign: Right
Label	Interest Rate:
TextBox	0.00	txtInterestRae	TextAlign: Right
Label	%
Label	Number of Months:
TextBox	0	txtMonths	TextAlign: Right
Button	Calculate	btnCalculate
Label	Interest Amt:
TextBox	0.00	txtInterestAmount	TextAlign: Right
Label	Future Value:
TextBox	0.00	txtFutureValue	TextAlign: Right
Label	Monthly Payment:
TextBox	0.00	txtMonthlyPayment	TextAlign: Right
Label	Notes		BackColor: Gray AutoSize: False
TextBox		txtNotes	Multiline: True ScrollBars: Vertical
Button	Submit	btnSubmit
Button	Close	btnClose

Double-click an unoccupied area of the body of the form

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WattsALoan2
{
    public partial class NewLoanAllocation : Form
    {
        public NewLoanAllocation()
        {
            InitializeComponent();
        }

        internal void GetLoanTypes()
        {
            using (SqlConnection cntWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                  "Database='WattsALoan1';" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdLoanTypes =
               new SqlCommand("SELECT LoanType FROM Loans.Types;", cntWattsALoan);

                cntWattsALoan.Open();
                SqlDataReader sdrLoanTypes = cmdLoanTypes.ExecuteReader();

                cbxLoanTypes.Items.Clear();

                while (sdrLoanTypes.Read())
                    cbxLoanTypes.Items.Add(sdrLoanTypes[0].ToString());
            }
        }

        private void NewLoanAllocation_Load(object sender, EventArgs e)
        {
            GetLoanTypes();
        }
    }
}

Set the Name to LoanAllocationEditor
Click Add

Design the form as follows:

Control	Text	Name	Other Properties
Label	Loan Number:
TextBox		txtLoanNumber
Button	Find	btnFind
Label	Date Prepared:
DateTimePicker		dtDatePrepared
Label	Prepared By		BackColor: Gray AutoSize: False
Label	Employee #:
MaskedTextBox		txtEmployeeNumber	Mask: 00-000
TextBox		txtEmployeeName
Label	Prepared For		BackColor: Gray AutoSize: False
Label	Account #:
MaskedTextBox		txtAccountNumber	Mask: 0000-0000
TextBox		txtCustomerName
Label	Loan Preparation		BackColor: Gray AutoSize: False
Label	Loan Type
ComboBox		cbxLoanTypes
Button	New Type of Loan	btnLoansTypes
Label	Loan Amount:
TextBox	0.00	txtPresentValue	TextAlign: Right
Label	Interest Rate:
TextBox	0.00	txtInterestRae	TextAlign: Right
Label	%
Label	Number of Months:
TextBox	0	txtMonths	TextAlign: Right
Button	Calculate	btnCalculate
Label	Interest Amt:
TextBox	0.00	txtInterestAmount	TextAlign: Right
Label	Future Value:
TextBox	0.00	txtFutureValue	TextAlign: Right
Label	Monthly Payment:
TextBox	0.00	txtMonthlyPayment	TextAlign: Right
Label	Notes		BackColor: Gray AutoSize: False
TextBox		txtNotes	Multiline: True ScrollBars: Vertical
Button	Submit	btnSubmit
Button	Close	btnClose

Double-click an unoccupied area of the body of the form

Implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WattsALoan2
{
    public partial class LoanAllocationEditor : Form
    {
        public LoanAllocationEditor()
        {
            InitializeComponent();
        }

        internal void GetLoanTypes()
        {
            using (SqlConnection cntWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                   "Database='WattsALoan1';" +
                                   "Integrated Security=SSPI;"))
            {
                SqlCommand cmdLoanTypes =
               new SqlCommand("SELECT LoanType FROM Loans.Types;", cntWattsALoan);

                cntWattsALoan.Open();
                SqlDataReader sdrLoanTypes = cmdLoanTypes.ExecuteReader();

                cbxLoanTypes.Items.Clear();

                while (sdrLoanTypes.Read())
                    cbxLoanTypes.Items.Add(sdrLoanTypes[0].ToString());
            }
        }

        private void LoanAllocationEditor_Load(object sender, EventArgs e)
        {
            GetLoanTypes();
        }
    }
}

To create a new form, on the main menu, click PROJECT -> Add Windows Form...
Set the Name to LoansAllocations
Click Add

Design the form as follows:

Control	(Name)	Anchor	Text
DataGridView	dgvLoansAllocations	Top, Bottom, Left, Right
Button	btnNewLoanAllocation	Bottom, Left	New Loan Allocation...
Button	btnLoanAllocationEditor	Bottom, Right	Loan Allocation Editor...
Button	btnClose	Bottom, Right	Close

Double-click an unoccupied area of the form
Return to the form and double-click the New Loan Allocation button
Return to the form and double-click the Loan Allocation Editor button
Return to the form and double-clic the Close button

Chgange the document as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WattsALoan2
{
    public partial class LoansAllocations : Form
    {
        public LoansAllocations()
        {
            InitializeComponent();
        }

	private void ShowAllocations()
	{
	    using (SqlConnection scWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                   "Database='WattsALoan1';" +
                                   "Integrated Security=yes;"))
            {
                SqlCommand cmdLoansAllocations =
                            new SqlCommand("Loans.GetAllocations",
                                           scWattsALoan);
     SqlDataAdapter sdaLoansAllocations = new SqlDataAdapter(cmdLoansAllocations);
                DataSet dsLoansAllocations = new DataSet("LoansAllocationsSet");

                scWattsALoan.Open();

                sdaLoansAllocations.Fill(dsLoansAllocations);
                dgvLoansAllocations.DataSource = dsLoansAllocations.Tables[0];
            }
        }
        
        private void LoansAllocations_Load(object sender, EventArgs e)
        {
            ShowAllocations();
        }

        private void btnNewLoanAllocation_Click(object sender, EventArgs e)
        {
            NewLoanAllocation nla = new NewLoanAllocation();
            nla.ShowDialog();
            ShowAllocations();
        }

        private void btnLoanAllocationEditor_Click(object sender, EventArgs e)
        {
            LoanAllocationEditor lae = new LoanAllocationEditor();
            lae.ShowDialog();
            ShowAllocations();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

To create a new form, on the main menu, click PROJECT -> Add Windows Form...

Set the Name to NewLoanPayment

Click Add

Design the form as follows:

Control	Text	Name	Other Properties
Label	Payment Date:
DateTimePicker		dtPaymentDate
Label	Payment Processed By		BackColor: Gray AutoSize: False
Label	Employee #:
MaskedTextBox		txtEmployeeNumber	Mask: 000-000
TextBox		txtEmployeeName
Label	Payment Processed For		BackColor: Gray AutoSize: False
Label	Loan Details		BackColor: Gray AutoSize: False
Label	Loan Number:
TextBox		txtLoanNumber
Label	Payment Amount:
TextBox	0.00	txtPaymentAmount
TextBox		txtLoanDetails	Multiline: True ScrollBars: Vertical
Label	Previous Balance:
TextBox		txtPreviousBalance
Label	Current Balance:
TextBox	0.00	txtCurrentBalance
Label	Notes
TextBox		txtNotes	Multiline: True ScrollBars: Vertical
Button	Submit	btnSubmit
Button	Close	btnClose

To create a new form, on the main menu, click PROJECT -> Add Windows Form...

Set the Name to LoansPayments

Click Add

Design the form as follows:

Control	(Name)	Text	Anchor
DataGridView	dgvLoansPayments		Top, Bottom, Left, Right
Button	btnNewPayment	New Payment...	Bottom, Left
Button	btnClose	Close	Bottom, Right

Double-click an unoccupied area of the form

Return to the form and double-click the New Payment button

Return to the form and double-click the Close button

Change the document as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WattsALoan2
{
    public partial class LoansPayments : Form
    {
        public LoansPayments()
        {
            InitializeComponent();
        }

        private void ShowPayments()
        {
            using (SqlConnection scWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                  "Database='WattsALoan1';" +
                                  "Integrated Security=yes;"))
            {
                SqlCommand cmdLoansPayments =
                            new SqlCommand("Loans.GetPayments",
                                           scWattsALoan);
           SqlDataAdapter sdaLoansPayments = new SqlDataAdapter(cmdLoansPayments);
                DataSet dsLoansAllocations = new DataSet("LoansAllocationsSet");

                scWattsALoan.Open();

                sdaLoansPayments.Fill(dsLoansAllocations);
                dgvLoansPayments.DataSource = dsLoansAllocations.Tables[0];
            }
        }

        private void LoansPayments_Load(object sender, EventArgs e)
        {
            ShowPayments();
        }

        private void btnNewPayment_Click(object sender, EventArgs e)
        {
            NewLoanPayment lpn = new NewLoanPayment();
            lpn.ShowDialog();

	    ShowPayments();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}

Access the WattsALoan form

Design the form as follows:

Control	(Name)	Name
Button	btnCustomers	Customers
Button	btnLoansAllocations	Loans Allocations...
Button	btnEmployees	Employees
Button	btnLoansPayments	Loans Payments...
Button	Close	btnClose

Double-click the Customers button

Implement its event as follows:

private void btnCustomers_Click(object sender, EventArgs e)
{
    Customers clients = new Customers();
    clients.Show();
}

Return to the WattsALoan form and double-click the Loans Allocations button

Implement the event as follows:

private void btnLoansAllocations_Click(object sender, EventArgs e)
{
    LoansAllocations las = new LoansAllocations();
    las.Show();
}

Return to the form and double-click the Employees button

Implement its event as follows:

private void btEmployees_Click(object sender, EventArgs e)
{
    Employees clerks = new Employees();
    clerks.Show();
}

Return to the form and double-click the Loans Payments button

Implement the event as follows:

private void btnLoansAllocations_Click(object sender, EventArgs e)
{
    LoansPayments lps = new LoansPayments ();
    lps.Show();
}

Return to the form and double-click the Close button

Implement its event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Modifying a Stored Procedure

As a regular SQL Server database object, you can modify a stored procedure without recreating it. In SQL, the basic formula to modify a stored procedure is:

ALTER PROCEDURE ProcedureName
AS
BEGIN
    Body of the Procedure
END

You can create an expression and use it in the SELECT statement of a stored procedure. Here is an example:

CREATE PROCEDURE GetStudentIdentification
AS
BEGIN
    SELECT FullName = FirstName + N' ' + LastName,
           DateOfBirth, Gender
    FROM Students
END

Also, a stored procedure can call a function in its body. Here is an example:

CREATE PROCEDURE GetStudentsAges
AS
BEGIN
    SELECT FullName = FirstName + N' ' + LastName,
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
           Gender
    FROM Students
END

Deleting a Stored Procedure

One of the characteristics of a stored procedure is that it is treated like an object in its own right. To delete a stored procedure in Transact-SQL, the syntax to use is:

DROP PROCEDURE [SchemaName.]ProcedureName

Of course, you should make sure you are in the right database and also that the ProcedureName exists.

Introduction to Arguments of a Stored Procedure

Passing Arguments

Like a method of a class in traditional programming languages (C#, Java, etc), and like a function in Transact-SQL (and C++, Visual Basic, Pascal, etc), a stored procedure can take 0, 1, 2, or more arguments. To create a stored procedure that takes an argument, type the formula CREATE PROCEDURE or CREATE PROC followed by the name of the procedure. Then, type the name of an argument starting with the @ symbol. The parameter must have a name, a data type and an optional length depending on the type. Here is the formula you would use:

CREATE PROCEDURE [SchemaName.]ProcedureName
	@ParameterName DataType
AS
BEGIN
    Body of the Procedure
END

Here is an example:

CREATE PROC Registrations.GetListOfStudentsByGender
       @gdr NVARCHAR(12)
AS
SELECT FirstName, LastName,
       DateOfBirth, HomePhone, Gender
FROM   Students
WHERE  Gender = @gdr

Practical Learning: Creating a Stored Procedure

Change code of the WattsALoan form as follows:

private void CreateStoredProcedures()
{
    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1'; " +
              "Integrated Security='SSPI';"))
    {
        SqlCommand cmdWattsALoan =
            new SqlCommand("CREATE PROCEDURE Personnel.GetEmployeeName " +
                           "    @EmplNbr nchar(7) " +
                           "AS " +
                           "    SELECT FullName FROM Personnel.Employees " +
                           "    WHERE EmployeeNumber = @EmplNbr;",
                           cntWattsALoan);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();
    }
    
    using (SqlConnection cntWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                   "Database='WattsALoan1'; " +
                                   "Integrated Security='SSPI';"))
    {
        SqlCommand cmdWattsALoan =
                new SqlCommand("CREATE PROCEDURE Accounts.GetCustomerName " +
                               "    @AcntNbr nchar(12) " +
                               "AS " +
                               "    SELECT CustomerName FROM Accounts.Customers " +
                               "    WHERE AccountNumber  = @AcntNbr;",
                               cntWattsALoan);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();
    }
    
    using (SqlConnection cntWattsALoan =
    	new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1'; " +
              "Integrated Security='SSPI';"))
    {
        SqlCommand cmdWattsALoan =
            new SqlCommand("CREATE PROCEDURE Loans.GetLoanDetails " +
                           "        @LoanNbr int " +
                           "AS " +
                           "    SELECT la.DateAllocated, " +
                           "           la.AccountNumber, " +
                           "           la.LoanType, " +
                           "           la.LoanAmount, " +
                           "           la.MonthlyPayment " +
                           "    FROM   Loans.Allocations la " +
                           "    WHERE  la.LoanNumber = @LoanNbr; ",
                           cntWattsALoan);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();
    }

    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1'; " +
              "Integrated Security='SSPI';"))
    {
        SqlCommand cmdWattsALoan =
            new SqlCommand("CREATE PROCEDURE Loans.LocateLoanAllocation " +
                           "    @LoanNbr int " +
                           "AS " +
                           "    SELECT alocs.DateAllocated  [Date Allocated], " +
                           "           alocs.EmployeeNumber [Employee #], " +
                           "           empls.FullName       Employee, " +
                           "           alocs.AccountNumber  [Account #], " +
                           "           custs.CustomerName   Customer, " +
                           "           alocs.LoanType       [Type of Loan], " +
                           "           alocs.LoanAmount     Principal, " +
                           "           alocs.InterestRate   Rate, " +
                           "           alocs.Periods        Periods, " +
                           "           alocs.InterestAmount [Interest Paid], " +
                           "           alocs.FutureValue    [Future Value], " +
                           "           alocs.MonthlyPayment [Payment/Month], " +
                           "           alocs.Notes          Comments " +
                           "    FROM   Loans.Allocations alocs " +
                           "    INNER JOIN Personnel.Employees empls " +
                           "           ON alocs.EmployeeNumber = empls.EmployeeNumber " +
                           "    INNER JOIN Accounts.Customers custs " +
                           "           ON alocs.AccountNumber = custs.AccountNumber " +
                           "    WHERE  alocs.LoanNumber = @LoanNbr;",
                           cntWattsALoan);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();
    }

    MessageBox.Show("New stored procedures have been created.",
                    "Watts a Loan",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void WattsALoan_Load(object sender, EventArgs e)
{
    CreateStoredProcedures();
}

Execute the application
Click OK on the message box
Close the form and return to your programming environment

The Type of Command

There are two types of actions a SQL command can process. When using a command, before executing it, you must specify its type of action. To assist you with this, the command class is equipped with a property named CommandType. The CommandType property is based on the CommandType enumeration. One of the members of the CommandType enumeration is Text. This is applied for a regular SELECT operation and this is the default type used by the command. This means that if you don't specify the type, Text is applied. That has been the case for all the commands (SELECT statements and functions) we have used in previous sections and lessons.

To support stored procedured, the CommandType enumeration is equipped with a member named StoredProcedure. Therefore, if your command statement deals with a stored procedure, specify its CommandType as such. Here is an example:

private void btnSubmit_Click(object sender, EventArgs e)
{
    using (SqlConnection cnnStudents =
	new SqlConnection(strConnection))
    {
	SqlCommand cmdStudents = new SqlCommand(strStatement, cnnStudents);
	cmdStudents.CommandType = CommandType.Text;

	cmdStudents.CommandType = CommandType.StoredProcedure;

	. . .
    }
}

Executing a Stored Procedure With Arguments

As seen in previous sections, to call a stored procedure in Microsoft Visual Studio, right-click it in the Server Explorer and click Execute. If the procedure takes at least one argument, you must supply it. If you are working visually, a dialog box would come up to allow you to provide a value for the argument:

Visually Executing a Stored Procedure

In the same way, if you execute a stored procedure that takes more than one argument, in the Execute Stored Procedure dialog box, specify the desired but right value for each argument. The value must be of the appropriate type.

To programmatically deal with a stored procedure that takes an argument, first call it in your command as seen already. Then, create a SqlParameter object and pass it to the command. Here is an example:

private void btnSubmit_Click(object sender, EventArgs e)
{
    using (SqlConnection cnnStudents =
	new SqlConnection(strConnection))
    {
	SqlCommand cmdStudents = new SqlCommand(strStatement, cnnStudents);
	cmdStudents.CommandType = CommandType.Text;

	cmdStudents.CommandType = CommandType.StoredProcedure;
	prmStudent.ParameterName = "@StdNbr";
	prmStudent.DbType = DbType.String;
	prmStudent.Value = "60283";

	cmdStudents.Parameters.Add(prmStudent);

	cnnStudents.Open();

	. . .
    }
}

Practical Learning: Executing an Argumentative Procedure

Display the NewLoanPayment form
Click the Employee Number text box and, in the Properties window, click the Events button

Double-click the Leave button and implement the event as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WattsALoan2
{
    public partial class NewLoanPayment : Form
    {
        public NewLoanPayment()
        {
            InitializeComponent();
        }

        private void txtEmployeeNumber_Leave(object sender, EventArgs e)
        {
            using (SqlConnection cntWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                  "Database='WattsALoan1';" +
                                  "Integrated Security=SSPI;"))
            {
                SqlParameter spEmployee = new SqlParameter();
                SqlCommand cmdWattsALoan = new SqlCommand("Personnel.GetEmployeeName", cntWattsALoan);

                cmdWattsALoan.CommandType = CommandType.StoredProcedure;
                spEmployee.ParameterName = "@EmplNbr";
                spEmployee.DbType = DbType.String;
                spEmployee.Value = txtEmployeeNumber.Text;
                cmdWattsALoan.Parameters.Add(spEmployee);

                cntWattsALoan.Open();
                SqlDataReader rdrWattsALoan = cmdWattsALoan.ExecuteReader();

                while (rdrWattsALoan.Read())
                    txtEmployeeName.Text = rdrWattsALoan[0].ToString();
            }
        }
    }
}

Display the NewLoanAllocation form
Click the Employee Number text box and, in the Events section of the Properties window, double-click the Leave button

Implement the event as follows:

private void txtEmployeeNumber_Leave(object sender, EventArgs e)
{
    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
          "Database='WattsALoan1';" +
          "Integrated Security=SSPI;"))
    {
        SqlParameter spEmployee = new SqlParameter();
        SqlCommand cmdWattsALoan =
        	new SqlCommand("Personnel.GetEmployeeName", cntWattsALoan);

        cmdWattsALoan.CommandType = CommandType.StoredProcedure;
        spEmployee.ParameterName = "@EmplNbr";
        spEmployee.DbType = DbType.String;
        spEmployee.Value = txtEmployeeNumber.Text;
        cmdWattsALoan.Parameters.Add(spEmployee);

        cntWattsALoan.Open();
        SqlDataReader rdrWattsALoan = cmdWattsALoan.ExecuteReader();

        while (rdrWattsALoan.Read())
            txtEmployeeName.Text = rdrWattsALoan[0].ToString();
    }
}

Return to the NewLoanAllocation form
Click the Account Number text box
In the Events section of the Properties window, double-click Leave

Implement the event as follows:

private void txtAccountNumber_Leave(object sender, EventArgs e)
{
    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
      			  "Database='WattsALoan1';" +
      			  "Integrated Security=SSPI;"))
    {
        SqlParameter spEmployee = new SqlParameter();
        SqlCommand cmdWattsALoan = new SqlCommand("Accounts.GetCustomerName", cntWattsALoan);
        cmdWattsALoan.CommandType = CommandType.StoredProcedure;

        SqlParameter prmWattsALoan = new SqlParameter();
        prmWattsALoan.ParameterName = "@AcntNbr";
        prmWattsALoan.DbType = DbType.String;
        prmWattsALoan.Value = txtAccountNumber.Text;
        cmdWattsALoan.Parameters.Add(prmWattsALoan);

        cntWattsALoan.Open();
        SqlDataReader rdrWattsALoan = cmdWattsALoan.ExecuteReader();

        while (rdrWattsALoan.Read())
            txtCustomerName.Text = rdrWattsALoan[0].ToString();
    }
}

Return to the NewLoanAllocation form
Double-click the Calculate button

Implement its event as follows:

private void btnCalculate_Click(object sender, EventArgs e)
{
    double Principal = 0.00, InterestRate = 0.00,
	   Periods = 0.00, InterestAmount = 0.00,
	   FutureValue = 0.00, MonthlyPayment = 0.00;

    try
    {
	Principal = double.Parse(txtPresentValue.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Principal Amount");
    }

    try
    {
	InterestRate = double.Parse(txtInterestRate.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Interest Rate");
    }

    try
    {
    	Periods = double.Parse(txtMonths.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Number of Months");
    }

    InterestAmount = Principal * (InterestRate / 100) * Periods / 12;
    FutureValue    = Principal + InterestAmount;
    MonthlyPayment = FutureValue / Periods;

    txtInterestAmount.Text = InterestAmount.ToString("F");
    txtFutureValue.Text    = FutureValue.ToString("F");
    txtMonthlyPayment.Text = MonthlyPayment.ToString("F");
}

Display the Loan Allocation Editor form
Double-click the Find button

Implement the event as follows:

private void btnFind_Click(object sender, EventArgs e)
{
    using (SqlConnection cntWattsALoan =
                new SqlConnection("Data Source=(local);" +
                                  "Database='WattsALoan1';" +
                                  "Integrated Security=SSPI;"))
    {
        SqlCommand cmdLoansAllocations =
            new SqlCommand("Loans.LocateLoanAllocation", cntWattsALoan);
        cmdLoansAllocations.CommandType = CommandType.StoredProcedure;

        SqlParameter spLoanNumber = new SqlParameter();

       cmdLoansAllocations.Parameters.AddWithValue("@LoanNbr", txtLoanNumber.Text);

        cntWattsALoan.Open();
        SqlDataReader sdrLoanTypes = cmdLoansAllocations.ExecuteReader();

        while (sdrLoanTypes.Read())
        {
            dtpDateAllocated.Value = DateTime.Parse(sdrLoanTypes[0].ToString());
            txtEmployeeNumber.Text = sdrLoanTypes[1].ToString();
            txtEmployeeName.Text   = sdrLoanTypes[2].ToString();
            txtAccountNumber.Text  = sdrLoanTypes[3].ToString();
            txtCustomerName.Text   = sdrLoanTypes[4].ToString();
            cbxLoanTypes.Text      = sdrLoanTypes[5].ToString();
            txtLoanAmount.Text     = sdrLoanTypes[6].ToString();
            txtInterestRate.Text   = sdrLoanTypes[7].ToString();
            txtPeriods.Text        = sdrLoanTypes[8].ToString();
            txtInterestAmount.Text = sdrLoanTypes[9].ToString();
            txtFutureValue.Text    = sdrLoanTypes[10].ToString();
            txtMonthlyPayment.Text = sdrLoanTypes[11].ToString();
            txtNotes.Text          = sdrLoanTypes[12].ToString();
        }
    }
}

Return to the Loan Allocation Editor form
Click the Employee # text box and, in the Events section of the Properties window, double-click the Leave button

Implement the event as follows:

private void txtEmployeeNumber_Leave(object sender, EventArgs e)
{
    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
          "Database='WattsALoan1';" +
          "Integrated Security=SSPI;"))
    {
        SqlParameter spEmployee = new SqlParameter();
        SqlCommand cmdWattsALoan =
        	new SqlCommand("Personnel.GetEmployeeName", cntWattsALoan);

        cmdWattsALoan.CommandType = CommandType.StoredProcedure;
        spEmployee.ParameterName = "@EmplNbr";
        spEmployee.DbType = DbType.String;
        spEmployee.Value = txtEmployeeNumber.Text;
        cmdWattsALoan.Parameters.Add(spEmployee);

        cntWattsALoan.Open();
        SqlDataReader rdrWattsALoan = cmdWattsALoan.ExecuteReader();

        while (rdrWattsALoan.Read())
            txtEmployeeName.Text = rdrWattsALoan[0].ToString();
    }
}

Return to the Loan Allocation Editor form
Click the Account # text box and, in the Events section of the Properties window, double-click Leave

Implement the event as follows:

private void txtAccountNumber_Leave(object sender, EventArgs e)
{
    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
      			   "Database='WattsALoan1';" +
      			   "Integrated Security=SSPI;"))
    {
        SqlParameter spEmployee = new SqlParameter();
        SqlCommand cmdWattsALoan = new SqlCommand("Accounts.GetCustomerName", cntWattsALoan);
        cmdWattsALoan.CommandType = CommandType.StoredProcedure;

        SqlParameter prmWattsALoan = new SqlParameter();
        prmWattsALoan.ParameterName = "@AcntNbr";
        prmWattsALoan.DbType = DbType.String;
        prmWattsALoan.Value = txtAccountNumber.Text;
        cmdWattsALoan.Parameters.Add(prmWattsALoan);

        cntWattsALoan.Open();
        SqlDataReader rdrWattsALoan = cmdWattsALoan.ExecuteReader();

        while (rdrWattsALoan.Read())
            txtCustomerName.Text = rdrWattsALoan[0].ToString();
    }
}

Return to the Loan Allocation Editor form
Double-click the Calculate button

Implement its event as follows:

private void btnCalculate_Click(object sender, EventArgs e)
{
    double Principal = 0.00, InterestRate = 0.00,
	   Periods = 0.00, InterestAmount = 0.00,
	   FutureValue = 0.00, MonthlyPayment = 0.00;

    try
    {
	Principal = double.Parse(txtLoanAmount.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Principal Amount",
                        "Watts A Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    try
    {
	InterestRate = double.Parse(txtInterestRate.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Interest Rate",
                        "Watts A Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    try
    {
    	Periods = double.Parse(txtPeriods.Text);
    }
    catch (FormatException)
    {
	MessageBox.Show("Invalid Number of Months",
                        "Watts A Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    InterestAmount = Principal * (InterestRate / 100) * Periods / 12;
    FutureValue    = Principal + InterestAmount;
    MonthlyPayment = FutureValue / Periods;

    txtInterestAmount.Text = InterestAmount.ToString("F");
    txtFutureValue.Text    = FutureValue.ToString("F");
    txtMonthlyPayment.Text = MonthlyPayment.ToString("F");
}

A Stored Procedure With Many Arguments

As opposed to one, a stored procedure can take many parameters. To create such a procedure, make the list of arguments, each with its own name that starts with @, a name, and a data type. When calling the procedure, process each parametert as we have done so far. Here are examples:

private void btnSubmit_Click(object sender, EventArgs e)
{
    using (SqlConnection cntWattsALoan =
	new SqlConnection("Data Source=(local); " +
			  "Database='WattsALoan1'; " +
			  "Integrated Security='SSPI';"))
    {
	SqlCommand cmdWattsALoan = 
	    new SqlCommand("Loans.SpecifyCurrentBalance", 
			   cntWattsALoan);
	cmdWattsALoan.CommandType = CommandType.StoredProcedure;

	SqlParameter prmWattsALoan = new SqlParameter();
	prmWattsALoan.ParameterName = "@PmtDate";
	prmWattsALoan.DbType = DbType.DateTime;
	prmWattsALoan.Value = dtpPaymentDate.Value.ToString("d");
	cmdWattsALoan.Parameters.Add(prmWattsALoan);

	prmWattsALoan = new SqlParameter();
	prmWattsALoan.ParameterName = "@EmplNbr";
	prmWattsALoan.DbType = DbType.String;
	prmWattsALoan.Value = txtEmployeeNumber.Text;
	cmdWattsALoan.Parameters.Add(prmWattsALoan);

	prmWattsALoan = new SqlParameter();
	prmWattsALoan.ParameterName = "@LoanNbr";
	prmWattsALoan.DbType = DbType.Int32;
	prmWattsALoan.Value = int.Parse(txtLoanNumber.Text);
	cmdWattsALoan.Parameters.Add(prmWattsALoan);

	prmWattsALoan = new SqlParameter();
	prmWattsALoan.ParameterName = "@PmtAmt";
	prmWattsALoan.DbType = DbType.Double;
	prmWattsALoan.Value = double.Parse(txtPaymentAmount.Text);
	cmdWattsALoan.Parameters.Add(prmWattsALoan);

	prmWattsALoan = new SqlParameter();
	prmWattsALoan.ParameterName = "@Comments";
	prmWattsALoan.DbType = DbType.String;
	prmWattsALoan.Value = txtNotes.Text;
	cmdWattsALoan.Parameters.Add(prmWattsALoan);

	cntWattsALoan.Open();
	cmdWattsALoan.ExecuteNonQuery();
    }
}

Practical Learning: Using a Stored Procedure With Many Parameters

Display the WattsALoan form
Double-click an unoccupied area of its body

Change the code of the CreateStoredProcedures method as follows:

private void CreateStoredProcedures2()
{
    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1'; " +
              "Integrated Security='SSPI';"))
    {
        SqlCommand cmdWattsALoan =
            new SqlCommand("CREATE PROCEDURE Loans.CreateLoanAllocation " +
                           "	@DteAllocated date,     	@EmplNbr      nchar(7),  @AcntNbr    nchar(12)," +
                           "	@TypeOfLoan   nvarchar(40), @Principal	  money,     @Percentage decimal(6, 2)," +
                           "	@LoanLength   decimal(6,2), @InterestPaid money,     @FValue     money," +
                           "	@PaymentPerMonth money,     @Comments nvarchar(max)" +
                           "AS " +
                           "    INSERT INTO Loans.Allocations(DateAllocated, EmployeeNumber, AccountNumber," +
                           "                                  LoanType, LoanAmount, InterestRate, Periods," +
                           "                                  InterestAmount, FutureValue, MonthlyPayment, Notes)" +
                           "	VALUES(@DteAllocated, @EmplNbr, @AcntNbr, @TypeOfLoan, @Principal, @Percentage," +
                           "		   @LoanLength, @InterestPaid, @FValue, @PaymentPerMonth, @Comments);",
                           cntWattsALoan);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();
    }

    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1'; " +
              "Integrated Security='SSPI';"))
    {
        SqlCommand cmdWattsALoan =
            new SqlCommand("CREATE PROCEDURE Loans.UpdateLoanAllocation " +
                           "    @LoanNbr int, " +
                           "    @DteAllocated date, @EmplNbr nchar(7), " +
                           "    @AcntNbr nchar(12), @TypeOfLoan nvarchar(40), " +
                           "    @Principal	money,  @Percentage decimal(6, 2), " +
                           "    @LoanLength decimal(6,2), @InterestPaid money, " +
                           "    @FValue money,  	@PaymentPerMonth money, @Comments nvarchar(max) " +
                           "AS " +
                           "    UPDATE Loans.Allocations SET DateAllocated  = @dteAllocated    WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET EmployeeNumber = @EmplNbr         WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET AccountNumber  = @AcntNbr         WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET LoanType       = @TypeOfLoan      WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET LoanAmount     = @Principal       WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET InterestRate   = @Percentage      WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET Periods        = @LoanLength      WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET InterestAmount = @InterestPaid    WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET FutureValue    = @FValue          WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET MonthlyPayment = @PaymentPerMonth WHERE LoanNumber = @LoanNbr;" +
                           "    UPDATE Loans.Allocations SET Notes          = @Comments        WHERE LoanNumber = @LoanNbr;",
                           cntWattsALoan);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();
    }

    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1'; " +
              "Integrated Security='SSPI';"))
    {
        SqlCommand cmdWattsALoan =
            new SqlCommand("CREATE PROCEDURE Loans.MakeLoanPayment " +
                           "	@DtePaid date,     @EmplNbr    nchar(7), @LnNbr   int," +
                           "	@CurrentPmt money, @CurrentBal money,    @Comments nvarchar(max)" +
                           "AS " +
                           "    INSERT INTO Loans.Payments(PaymentDate, EmployeeNumber, LoanNumber," +
                           "                               PaymentAmount, Balance, Notes)" +
                           "	VALUES(@DtePaid, @EmplNbr, @LnNbr, @CurrentPmt, @CurrentBal, @Comments);",
                           cntWattsALoan);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();
    }

    MessageBox.Show("New stored procedures have been created.",
                    "Watts a Loan",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void WattsALoan_Load(object sender, EventArgs e)
{
    CreateStoredProcedures2();
}

Execute the application
Click OK on the message box
Close the form and return to your programming environment
In the Load event of the Watts A Loan form, delete CreateStoredProcedures2(); as follows:
```
private void WattsALoan_Load(object sender, EventArgs e)
{
    
}
```
Display the New Loan Allocation form

Double-click the Submit button and implement the event as follows:

private void btnSubmit_Click(object sender, EventArgs e)
{
    // Don't save the loan allocation if there is no employee number
    if (txtEmployeeName.Text.Length == 0)
    {
        MessageBox.Show("You must enter a valid employee number.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    // Don't save the loan allocation if we can't identify the customer with an account number
    if (txtCustomerName.Text.Length == 0)
    {
        MessageBox.Show("You must enter a valid account number.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    // Make sure the user has specified the type of loan
    if (cbxLoanTypes.Text.Length == 0)
    {
        MessageBox.Show("You must specify the type of loan.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    if (txtPresentValue.Text.Length == 0)
    {
        MessageBox.Show("You must specify the amount that is being lent.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    if (txtInterestRate.Text.Length == 0)
    {
        MessageBox.Show("You must specify the interest rate of the loan.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    if (txtMonths.Text.Length == 0)
    {
        MessageBox.Show("You must specify the number of months as period of the loan.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    // Before saving the loan, just in case, perform the calculation
    btnCalculate_Click(sender, e);

    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1';" +
              "Integrated Security=SSPI;"))
    {
        SqlParameter spLoanAllocation = new SqlParameter();
        SqlCommand cmdWattsALoan = new SqlCommand("Loans.CreateLoanAllocation", cntWattsALoan);
        cmdWattsALoan.CommandType = CommandType.StoredProcedure;

        spLoanAllocation.ParameterName = "@DteAllocated";
        spLoanAllocation.DbType = DbType.Date;
        spLoanAllocation.Value = dtpDateAllocated.Value.ToShortDateString();
        spLoanAllocation.SourceColumn = "DateAllocated";
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@EmplNbr", txtEmployeeNumber.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@AcntNbr", txtAccountNumber.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@TypeOfLoan", cbxLoanTypes.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@Principal", txtPresentValue.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@Percentage", txtInterestRate.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@LoanLength", txtMonths.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@InterestPaid", txtInterestAmount.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@FValue", txtFutureValue.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@PaymentPerMonth", txtMonthlyPayment.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);
        spLoanAllocation = new SqlParameter("@Comments", txtNotes.Text);
        cmdWattsALoan.Parameters.Add(spLoanAllocation);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();

        MessageBox.Show("A new loan has been created and issued.",
                            "Watts a Loan",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    Close();
}

Return to the New Loan Allocation form and double-click the Close button

Implement the event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Display the LoanAllocationEditor form

Double-click the Submit button and implement the event as follows:

private void btnSubmit_Click(object sender, EventArgs e)
{
    btnCalculate_Click(sender, e);

    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1';" +
              "Integrated Security=SSPI;"))
    {
        SqlParameter spLoanUpdate = new SqlParameter();
        SqlCommand cmdWattsALoan = new SqlCommand("Loans.UpdateLoanAllocation", cntWattsALoan);
        cmdWattsALoan.CommandType = CommandType.StoredProcedure;

        spLoanUpdate = new SqlParameter();
        spLoanUpdate.ParameterName = "@LoanNbr";
        spLoanUpdate.DbType = DbType.Int32;
        spLoanUpdate.Value = txtLoanNumber.Text;
        cmdWattsALoan.Parameters.Add(spLoanUpdate);

        spLoanUpdate = new SqlParameter();
        spLoanUpdate.ParameterName = "@DteAllocated";
        spLoanUpdate.DbType = DbType.Date;
        spLoanUpdate.Value = dtpDateAllocated.Value.ToShortDateString();
        cmdWattsALoan.Parameters.Add(spLoanUpdate);

        spLoanUpdate = new SqlParameter("@EmplNbr", txtEmployeeNumber.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@AcntNbr", txtAccountNumber.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@TypeOfLoan", cbxLoanTypes.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@Principal", txtLoanAmount.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@Percentage", txtInterestRate.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@LoanLength", txtPeriods.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@InterestPaid", txtInterestAmount.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@FValue", txtFutureValue.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@PaymentPerMonth", txtMonthlyPayment.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
        spLoanUpdate = new SqlParameter("@Comments", txtNotes.Text);
        cmdWattsALoan.Parameters.Add(spLoanUpdate);
                           
        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();

        MessageBox.Show("The loan has been updated.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);

        // Reset the form
        txtLoanNumber.Text = "";
        dtpDateAllocated.Value = DateTime.Today;
        txtEmployeeNumber.Text = "";
        txtEmployeeName.Text = "";
        txtAccountNumber.Text = "";
        txtCustomerName.Text = "";
        cbxLoanTypes.SelectedIndex = 0;
        txtLoanAmount.Text = "0.00";
        txtInterestRate.Text = "0.00";
        txtPeriods.Text = "0";
        txtInterestAmount.Text = "0.00";
        txtFutureValue.Text = "0.00";
        txtMonthlyPayment.Text = "0.00";
        txtNotes.Text = "";
    }
}

Return to the Loan Allocation Editor form and double-click the Close button

Implement the event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Display the New Loan Payment form

Double-click the Submit button and implement the event as follows:

private void btnSubmit_Click(object sender, EventArgs e)
{
    if (string.IsNullOrEmpty(txtEmployeeName.Text))
    {
        MessageBox.Show("You must enter a valid employee number.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    if (string.IsNullOrEmpty(txtLoanNumber.Text))
    {
        MessageBox.Show("You must specify the loan number.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    if (string.IsNullOrEmpty(txtPaymentAmount.Text))
    {
        MessageBox.Show("You must specify the amount paid.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    if (string.IsNullOrEmpty(txtCurrentBalance.Text))
    {
        MessageBox.Show("You must specify the new balance.",
                            "Watts a Loan",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        return;
    }

    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
              "Database='WattsALoan1';" +
              "Integrated Security=SSPI;"))
    {
        SqlCommand cmdWattsALoan = new SqlCommand("Loans.MakeLoanPayment", cntWattsALoan);
        cmdWattsALoan.CommandType = CommandType.StoredProcedure;

        SqlParameter spPaymentDate = new SqlParameter("@DtePaid", dtpPaymentDate.Value.ToShortDateString());
        SqlParameter spEmployeeNumber = new SqlParameter("@EmplNbr", txtEmployeeNumber.Text);
        SqlParameter spLoanNumber = new SqlParameter("@LnNbr", txtLoanNumber.Text);
        SqlParameter spCurrentPayment = new SqlParameter("@CurrentPmt", txtPaymentAmount.Text);
        SqlParameter spCurrentBalance = new SqlParameter("@CurrentBal", txtCurrentBalance.Text);
        SqlParameter spNotes = new SqlParameter("@Comments", txtNotes.Text);
        cmdWattsALoan.Parameters.AddRange(new SqlParameter[] { spPaymentDate, spEmployeeNumber, spLoanNumber,
                                                                       spCurrentPayment, spCurrentBalance, spNotes });

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();

        MessageBox.Show("A new loan payment has been made.",
                        "Watts a Loan",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    Close();
}

Execute the application
Click the Loans Allocations button
Click the New Loan Allocation button and enter the following values:
Date Allocated: 2/6/2014
Employee #: 429-374
Account #: 29-750024-82
Loan Type: Personal Loan
Loan Amount: 6500
Interest Rate: 12.65
Periods: 36.00
Notes: The customer simply walked to the office and applied for a personal loan. The loan was granted.
Click Calculate
Click Submit

Create the following additional allocations (click Calculate whenever you have entered the information, then click Submit):

Date Allocated	Employee #	Account #	Loan Type	Loan Amount	Interest Rate	Periods	Notes
4/22/2014	492-947	64-827095-07	Boat Purchase	16500	10.20	60.00	The loan ws processed successfully.
4/25/2014	429-374	83-401857-88	Furniture	2455	14.88	36
6/4/2014	836-486	92-738024-35	Car Financing	20545	16.25	48

Click Loan Allocation Editor button and change the following allocations (the values to change are in bold characters) (click Calculate when the numeric values have changed, then click Submit):

Loan #	Date Allocated	Processed By	Account #	Loan Type	Loan Amount	Interest Rate	Periods	Notes
100004	4/25/2014	429-374	83-401857-88	Musical Instrument	2258.75	12.25	36	This loan was originally processed through the Washington Music Center.
100002	6/24/2014	293-747	92-738024-35	Car Financing	22748	10.25	60	This loan is to finance a Honda Civic with a Silver Spring Honda dealer.

Close the forms and return to your programming environment

Parameter Direction

As you should know from C#, a method takes various types of arguments. Like a method in C#, A stored procedure can take various types of arguments. To support that concept, the SqlParameter class is equipped with a property named Direction, which is of type ParameterDirection. As seen in the methods of a C# class, a stored procedure can take reference parameters meant to hold a value when the procedure ends. Such an argument is referred to as out.

The members of the ParameterDirection enumeration are:

Member Name	Description
Input	The parameter is used to pass a value to the stored procedure. You must spcfy the value of this parameter when calling the procedure
Output	The stored procedure will return the value of this parameter
InputOutput	The parameter can be used to pass a value to the stored procedure and it can be used to hold a value from the procedure
ReturnValue	The parameter is used to return a value from the stored procedure

To specify the direction of a parameter, access its Direction and call the appropriate member of the ParameterDirection enumeration. Here is an example from our New Loan Allocation form:

private void txtEmployeeNumber_Leave(object sender, EventArgs e)
{
    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
          "Database='WattsALoan1';" +
          "Integrated Security=SSPI;"))
    {
        SqlParameter spEmployee = new SqlParameter();
        SqlCommand cmdWattsALoan = new SqlCommand("Personnel.GetEmployeeName", cntWattsALoan);

        cmdWattsALoan.CommandType = CommandType.StoredProcedure;
        spEmployee.ParameterName = "@EmplNbr";
        spEmployee.DbType = DbType.String;
        spEmployee.Direction = ParameterDirection.Input;
        spEmployee.Value = txtEmployeeNumber.Text;
        cmdWattsALoan.Parameters.Add(spEmployee);

        cntWattsALoan.Open();
        SqlDataReader rdrWattsALoan = cmdWattsALoan.ExecuteReader();

        while (rdrWattsALoan.Read())
            txtEmployeeName.Text = rdrWattsALoan[0].ToString();
    }
}

If you want to pass an output argument to a stored procedure, when creating the procedure, add the OUTPUT keyword to the argument. Here is an example:

CREATE PROCEDURE Loans.GetCurrentBalance @LoanNbr int,
	@PreviousBalance money OUTPUT
AS
   BEGIN

   END;";

When creating the SQL parameter in your code, specify its direction but don't assign a value to the parameter.

Practical Learning: Speifying the Direction of Parameters

Display the WattsALoan form
Double-click an unoccupied area of its body

To create other stored procedures with arguments, change the document as follows:

private void CreateStoredProcedure2()
{
    using (SqlConnection cntWattsALoan =
        new SqlConnection("Data Source=(local);" +
                           "Database='WattsALoan1'; " +
                           "Integrated Security='SSPI';"))
    {
        string strWattsALoan =
                "CREATE PROCEDURE Loans.GetCurrentBalance @LoanNbr int, " +
                "                                         @PreviousBalance money OUTPUT " +
                "AS " +
                "   BEGIN " +
                "       SET @PreviousBalance = (SELECT MIN(pay.Balance) " +
                "           		        FROM   Loans.Payments pay " +
                "                               WHERE  pay.LoanNumber = @LoanNbr); " +
                "      IF @PreviousBalance IS NULL " +
                "           SET @PreviousBalance = (SELECT LoanAmount " +
                "                                   FROM Loans.Allocations " +
                "                                   WHERE LoanNumber = @LoanNbr); " +
                "   END;";

        SqlCommand cmdWattsALoan =
            new SqlCommand(strWattsALoan, cntWattsALoan);

        cntWattsALoan.Open();
        cmdWattsALoan.ExecuteNonQuery();
    }
       
    MessageBox.Show("A new stored procedure has been created.",
                    "Watts a Loan",
                    MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void WattsALoan_Load(object sender, EventArgs e)
{
    CreateStoredProcedure2();
}

Execute the application
Click OK on the message box
Close the form and return to your programming environment
In the Load event, delete CreateStoredProcedure2();
Display the New Loan Payment form and click the Loan Number text box

In the Events section of the Properties window, double-click Leave and implement the event as follows:

private void txtLoanNumber_Leave(object sender, EventArgs e)
{
    using (SqlConnection scWattsALoan =
        new SqlConnection("Data Source=(local);" +
                           "Database='WattsALoan1';" +
                           "Integrated Security=SSPI;"))
    {
        SqlParameter spEmployee = new SqlParameter();
        SqlCommand cmdWattsALoan = new SqlCommand("Loans.LocateLoanAllocation", scWattsALoan);

        cmdWattsALoan.CommandType = CommandType.StoredProcedure;
        spEmployee.ParameterName = "@LoanNbr";
        spEmployee.DbType = DbType.Int32;
        spEmployee.Value = int.Parse(txtLoanNumber.Text);
        cmdWattsALoan.Parameters.Add(spEmployee);

        scWattsALoan.Open();
        SqlDataReader rdrWattsALoan = cmdWattsALoan.ExecuteReader();

        while (rdrWattsALoan.Read())
        {
            txtLoanDetails.Text = "Date Allocated: " + rdrWattsALoan[0].ToString() + Environment.NewLine +
                                  "To " + rdrWattsALoan[3].ToString() + ": " + rdrWattsALoan[4].ToString() + Environment.NewLine +
                                  "Loan Type: " + rdrWattsALoan[5].ToString() + Environment.NewLine +
                                  "Loan Amount: " + rdrWattsALoan[6].ToString();
            txtPaymentAmount.Text = rdrWattsALoan[11].ToString();
        }
    }

    using (SqlConnection scWattsALoan =
        new SqlConnection("Data Source=(local);" +
                           "Database='WattsALoan1';" +
                           "Integrated Security=Yes"))
    {
        SqlCommand cmdLoansPayments = new SqlCommand("Loans.GetCurrentBalance", scWattsALoan);
        cmdLoansPayments.CommandType = CommandType.StoredProcedure;

        SqlParameter spLoanNumber = new SqlParameter();
        spLoanNumber.ParameterName = "@LoanNbr";
        spLoanNumber.DbType = DbType.Int32;
        spLoanNumber.Value = txtLoanNumber.Text;
        spLoanNumber.Direction = ParameterDirection.Input;
        cmdLoansPayments.Parameters.Add(spLoanNumber);

        SqlParameter spBalance = new SqlParameter();
        spBalance.ParameterName = "@PreviousBalance";
        spBalance.DbType = DbType.Double;
        spBalance.Direction = ParameterDirection.Output;
        cmdLoansPayments.Parameters.Add(spBalance);

        scWattsALoan.Open();
        cmdLoansPayments.ExecuteNonQuery();
        double balance = double.Parse(cmdLoansPayments.Parameters["@PreviousBalance"].Value.ToString());
        txtPreviousBalance.Text = balance.ToString();
    }

    // Calculate the new balance of the loan and display it
    txtCurrentBalance.Text = (double.Parse(txtPreviousBalance.Text) - double.Parse(txtPaymentAmount.Text)).ToString("F");
}

Return to the form and double-click its Close button

Implement its event as follows:

private void btnClose_Click(object sender, EventArgs e)
{
    Close();
}

Execute the application
Click the Loans Payments button

Click the New Payment button and create the following records:

Payment Date	Employee #	Loan #	Pmt Amt	Notes
3/25/2014	240-750	100001	249.08	This is the first payment
4/28/2014	804-685	100001	249.08
5/26/2014	804-685	100002	415.25	This is the first payment
5/30/2014	492-947	100003	85.80	This is the first payment
6/2/2014	429-374	100001	249.08
6/26/2014	836-486	100001	249.08
6/26/2014	836-486	100002	415.25
6/26/2014	804-685	100003	85.80
6/26/2014	836-486	100004	573.44	This is the first payment
7/25/2014	836-486	100002	415.25
7/31/2014	240-750	100003	85.80
7/31/2014	492-947	100004	573.44
8/5/2014	804-685	100001	498.16	The customer sent a double-payment for July and August.
8/10/2014	836-486	100002	450.25	This payment includes the regular monthly payment of $415.25 and a $35 payment for late fee.
8/27/2014	240-750	100003	85.80
8/28/2014	804-685	100004	573.44
8/31/2014	836-486	100002	450.25	This payment includes the regular monthly payment of $415.25 and a $35 payment for late fee.
9/3/2014	429-374	100002	415.25
9/22/2014	492-947	100002	415.25
9/25/2014	240-750	100004	573.44
9/30/2014	492-947	100001	249.08
10/2/2014	240-750	100002	415.25
10/25/2014	240-750	100003	204.24	This is a double-payment
10/26/2014	240-750	100001	249.08
10/26/2014	429-374	100002	415.25
10/30/2014	429-374	100004	573.44
10/31/2014	804-685	100002	415.25
11/26/2014	836-486	100004	573.44
11/28/2014	240-750	100001	249.08
11/28/2014	240-750	100002	415.25
11/30/2014	804-685	100003	85.80
12/22/2014	240-750	100002	890.50	This payment include two-month payment and 2 * $30 for late payments.
12/28/2014	492-947	100001	249.08
12/28/2014	429-374	100004	573.44
1/4/2015	836-486	100003	85.80
1/25/2015	804-685	100004	573.44
1/31/2015	836-486	100003	85.80
2/2/2015	836-486	100002	415.25
2/5/2015	836-486	100001	498.16	We received a double-payment for January 2015 and February 2015.
2/28/2015	836-486	100002	415.25
3/30/2015	429-374	100001	249.08
5/24/2015	492-947	100001	249.08

Close the form(s) and return to your programming environment

Default Arguments

Stored procedures use the concept of optional arguments as done in C#. To illustrate, imagine you create a database for a department store and a table that holds the list of items sold in the store:

ItemNumber	ItemCategoryID	ItemName	ItemSize	UnitPrice
264850	2	Long-Sleeve Jersey Dress	Petite	39.95
930405	4	Solid Crewneck Tee	Medium	12.95
293004	1	Cotton Comfort Open Bottom Pant	XLarge	17.85
924515	1	Hooded Full-Zip Sweatshirt	S	69.95
405945	3	Plaid Pinpoint Dress Shirt	22 35-36	35.85
294936	2	Cool-Dry Soft Cup Bra	36D	15.55
294545	2	Ladies Hooded Sweatshirt	Medium	45.75
820465	2	Cotton Knit Blazer	M	295.95
294694	2	Denim Blazer - Natural Brown	Large	75.85
924094	3	Texture-Striped Pleated Dress Pants	44x30	32.85
359405	3	Iron-Free Pleated Khaki Pants	32x32	39.95
192004	3	Sunglasses		15.85

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    DataGridView dgvStoreItems;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        dgvStoreItems = new DataGridView();
        dgvStoreItems.Location = new Point(12, 12);
        dgvStoreItems.Size = new System.Drawing.Size(268, 248);

        Text = "Department Store";
        Controls.Add(dgvStoreItems);
        Load += new EventHandler(FormLoaded);

        dgvStoreItems.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                               AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntStoreItems =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdStoreItems =
                new SqlCommand("SELECT ALL * FROM StoreItems;",
                cntStoreItems);

            cntStoreItems.Open();
            cmdStoreItems.ExecuteNonQuery();

            SqlDataAdapter sdaStoreItems = new SqlDataAdapter(cmdStoreItems);
            DataSet dsStoreItems = new DataSet("StoreItemsSet");

            sdaStoreItems.Fill(dsStoreItems);
            dgvStoreItems.DataSource = dsStoreItems.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Department Store: Store Items

Imagine you want to create a mechanism of calculating the price of an item after a discount has been applied to it. Such a procedure can be created as follows:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Stored Procedures";
        Load += new EventHandler(FormLoaded);
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
               new SqlCommand("CREATE PROCEDURE CalculateNetPrice @discount Decimal " +
                              "AS " +
                     "    SELECT ItemName, UnitPrice - (UnitPrice * @discount / 100) "+
                              "    FROM StoreItems;",
                cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();

            MessageBox.Show("The stored procedure has been created.",
                            "Exercise",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This can be executed as follows:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Stored Procedures";
        Load += new EventHandler(FormLoaded);
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("EXECUTE CalculateNetPrice;",
                cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

Running a Stored Procedure

If you are planning to create a procedure that takes an argument and you know that the argument will likely have the same value most of the time, you can provide that value as a parameter but leave a room for other values of that argument. A value given to an argument is referred to as default. What this implies is that, when the user calls that stored procedure, if the user does not provide a value for the argument, the SQL interpreter would use the default value.

To create a stored procedure that takes an argument that carries a default value, after declaring the value, on its right side, type = followed by the desired value. Here is an example applied to the above database:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Stored Procedures";
        Load += new EventHandler(FormLoaded);
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("DROP PROCEDURE CalculateNetPrice;", cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
        }

        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE PROCEDURE CalculateNetPrice @discount Decimal = 10.00 " +
                               "AS " +
                               "    SELECT ItemName, UnitPrice - (UnitPrice * @discount / 100) " +
                               "    FROM StoreItems;",
                cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();

            MessageBox.Show("The stored procedure has been created.",
                            "Exercise",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

When executing a procedure that takes a default argument, you do not have to provide a value for the argument if the default value suits you. Here is an example:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    DataGridView dgvStoreItems;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        dgvStoreItems = new DataGridView();
        dgvStoreItems.Location = new Point(12, 12);
        dgvStoreItems.Size = new System.Drawing.Size(268, 248);

        Text = "Department Store";
        Controls.Add(dgvStoreItems);
        Load += new EventHandler(FormLoaded);

        dgvStoreItems.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                               AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntStoreItems =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdStoreItems =
                new SqlCommand("EXECUTE CalculateNetPrice;",
                cntStoreItems);

            cntStoreItems.Open();
            cmdStoreItems.ExecuteNonQuery();

            SqlDataAdapter sdaStoreItems = new SqlDataAdapter(cmdStoreItems);
            DataSet dsStoreItems = new DataSet("StoreItemsSet");

            sdaStoreItems.Fill(dsStoreItems);
            dgvStoreItems.DataSource = dsStoreItems.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Department Store: Store Items

If the default value does not apply to your current calculation, you can provide a value for the argument.

You can create a procedure that takes more than one argument with default values. To provide a default value for each argument, after declaring it, type the desired value to its right side. Here is an example of a procedure that takes two arguments, each with a default value:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        Text = "Stored Procedures";
        Load += new EventHandler(FormLoaded);
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("DROP PROCEDURE CalculateNetPrice;", cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
        }

        using (SqlConnection cntExercise =
            new SqlConnection("Data Source=(local);" +
                              "Database='Exercise1';" +
                              "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE PROCEDURE CalculateNetPrice " +
                               "    @Discount Decimal = 20.00, " +
                               "    @TaxRate Decimal = 7.75 " +
                               "AS " +
                               "    SELECT  ItemName As [Item Description]," +
                               "            UnitPrice As [Marked Price]," +
                               "            UnitPrice * @Discount / 100 As [Discount Amt]," +
                               "            UnitPrice - (UnitPrice * @Discount / 100) As [After Discount]," +
                               "            UnitPrice * @TaxRate / 100 As [Tax Amount]," +
                               "            (UnitPrice * @TaxRate / 100) + UnitPrice - " +
                               "            (UnitPrice * @Discount / 100) + (@TaxRate / 100) As [Net Price] " +
                               "    FROM StoreItems;",
                cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();

            MessageBox.Show("The stored procedure has been created.",
                            "Exercise",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

When calling a procedure that takes more than one argument with all arguments having default values, you do not need to provide a value for each argument, you can provide a value for only one or some of the arguments. The above procedure can be called with one argument as follows:

EXEC CalculateSalePrice2 55.00

In this case, the other argument(s) would use its(their) default value(s). We saw that, when calling a procedure that takes more than one argument, you did not have to provide the values of the argument in the exact order they appeared in the procedure, you just had to type the name of each argument and assign it the desired value. In the same way, if a procedure takes more than one argument and some of the arguments have default values, when calling it, you can provide the values in the order of your choice, by typing the name of each argument and assigning it the desired value. Based on this, the above procedure can be called with only the value of the second argument as follows:

EXEC CalculateSalePrice2 @TaxRate = 8.55

In this case, the first argument would use its default value.


Home	Copyright © 2014-2022, FunctionX