Home

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 LearningPractical Learning: Introducing Stored Procedures

  1. Start Microsoft Visual Studio
  2. To create a new application, on the main menu, click FILE -> New -> Project...
  3. In the middle list, click Windows Forms Application
  4. Change the Name to WattsALoan2 and click OK
  5. In the Server Explorer, right-click any connection and click New Query
  6. Open the Watts A Loan1 file and select everything in it the document
  7. Copy and paste it in the Query window
  8. Right-click inside the document and click Execute
  9. In the Server Explorer, expand the WattsALoan1 connection
  10. In the Solution Explorer, right-click Form1.cs and click Rename
  11. 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 LearningPractical Learning: Creating Stored Procedures

  1. Display the WattsALoan form and double-click an unoccupied area of its body
  2. 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();
            }
        }
    }
  3. Execute the application
  4. Click OK on the message box
  5. Close the form and return to your programming environment
  6. To create a stored procedure, in the Server Explorer and under the WattsALoan1 node, right-click Stored Procedures and click Add New Stored Procedure
  7. 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
  8. To add the stored procedure to the database, under the tab, click the Update button Update
  9. A dialog box titled Preview Database Updates appears. After a few seconds, a few lines of text appear:
     
    Preview Database Updates
     
    In the bottom section of the Preview Database Updates dialog box, click Update Database
  10. Click inside the window, press Ctrl + A to select everything
  11. 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
  12. To add the stored procedure to the database, right-click inside the window and click Execute
  13. Click inside the window and press Ctrl + A
  14. To create another stored procedure, type the following code:
    CREATE PROCEDURE Loans.GetPayments
    AS
        SET NOCOUNT ON;
        SELECT ALL * FROM Loans.ShowPayments;
    GO
  15. To add the stored procedure to the database, under the tab, click the Update button Update
  16. 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 LearningPractical Learning: Executing Stored Procedures

  1. To create a new form, in the Solution Explorer, right-click WattsALoan2 -> Add -> Windows Form...
  2. Set the Name to Employees
  3. Click Add
  4. Design the form as follows:
     
    Watts A Loan - Employees
    Control (Name) Anchor Text
    DataGridView Data Grid View dgvEmployees Top, Bottom, Left, Right  
    Button Button btnClose Bottom, Right Close
  5. Double-click an unoccupied area of the form
  6. Return to the form and double-click the Close button
  7. 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();
            }
        }
    }
  8. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  9. Set the Name to Customers
  10. Click Add
  11. Design the form as follows:
     
    Watts A Loan - Customers
    Control (Name) Anchor Text
    DataGridView Data Grid View dgvCustomers Top, Bottom, Left, Right  
    Button Button btnClose Bottom, Right Close
  12. Double-click an unoccupied area of the form
  13. Return to the form and double-click the Close button
  14. change the document
  15. 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();
            }
        }
    }
  16. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  17. Set the Name to NewLoanAllocation
  18. Click Add
  19. Design the form as follows:
     
    Watts A Loan - Customers
    Control Text Name Other Properties
    Label Label Date Prepared:    
    DateTimePicker Date Time Picker   dtpDateAllocated  
    Label Label Prepared By   BackColor: Gray
    AutoSize: False
    Label Label Employee #:    
    MaskedTextBox Masked Text Box   txtEmployeeNumber Mask: 00-000
    TextBox Text Box   txtEmployeeName  
    Label Label Prepared For   BackColor: Gray
    AutoSize: False
    Label Label Account #:    
    MaskedTextBox Masked Text Box   txtAccountNumber Mask: 0000-0000
    TextBox Text Box   txtCustomerName  
    Label Label Loan Preparation   BackColor: Gray
    AutoSize: False
    Label Label Loan Type:    
    ComboBox Combo Box   cbxLoanTypes  
    Button Button New Type of Loan btnLoansTypes  
    Label Label Loan Amount:    
    TextBox Text Box 0.00 txtPresentValue TextAlign: Right
    Label Label Interest Rate:    
    TextBox Text Box 0.00 txtInterestRae TextAlign: Right
    Label Label %    
    Label Label Number of Months:    
    TextBox Text Box 0 txtMonths TextAlign: Right
    Button Button Calculate btnCalculate  
    Label Label Interest Amt:    
    TextBox Text Box 0.00 txtInterestAmount TextAlign: Right
    Label Label Future Value:    
    TextBox Text Box 0.00 txtFutureValue TextAlign: Right
    Label Label Monthly Payment:    
    TextBox Text Box 0.00 txtMonthlyPayment TextAlign: Right
    Label Label Notes   BackColor: Gray
    AutoSize: False
    TextBox Text Box   txtNotes Multiline: True
    ScrollBars: Vertical
    Button Button Submit btnSubmit  
    Button Button Close btnClose  
  20. Double-click an unoccupied area of the body of the form
  21. 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();
            }
        }
    }
  22. Set the Name to LoanAllocationEditor
  23. Click Add
  24. Design the form as follows:
     
    Watts A Loan - Customers
    Control Text Name Other Properties
    Label Label Loan Number:    
    TextBox Text Box   txtLoanNumber  
    Button Button Find btnFind  
    Label Label Date Prepared:    
    DateTimePicker Date Time Picker   dtDatePrepared  
    Label Label Prepared By   BackColor: Gray
    AutoSize: False
    Label Label Employee #:    
    MaskedTextBox Masked Text Box   txtEmployeeNumber Mask: 00-000
    TextBox Text Box   txtEmployeeName  
    Label Label Prepared For   BackColor: Gray
    AutoSize: False
    Label Label Account #:    
    MaskedTextBox Masked Text Box   txtAccountNumber Mask: 0000-0000
    TextBox Text Box   txtCustomerName  
    Label Label Loan Preparation   BackColor: Gray
    AutoSize: False
    Label Label Loan Type    
    ComboBox Combo Box   cbxLoanTypes  
    Button Button New Type of Loan btnLoansTypes  
    Label Label Loan Amount:    
    TextBox Text Box 0.00 txtPresentValue TextAlign: Right
    Label Label Interest Rate:    
    TextBox Text Box 0.00 txtInterestRae TextAlign: Right
    Label Label %    
    Label Label Number of Months:    
    TextBox Text Box 0 txtMonths TextAlign: Right
    Button Button Calculate btnCalculate  
    Label Label Interest Amt:    
    TextBox Text Box 0.00 txtInterestAmount TextAlign: Right
    Label Label Future Value:    
    TextBox Text Box 0.00 txtFutureValue TextAlign: Right
    Label Label Monthly Payment:    
    TextBox Text Box 0.00 txtMonthlyPayment TextAlign: Right
    Label Label Notes   BackColor: Gray
    AutoSize: False
    TextBox Text Box   txtNotes Multiline: True
    ScrollBars: Vertical
    Button Button Submit btnSubmit  
    Button Button Close btnClose  
  25. Double-click an unoccupied area of the body of the form
  26. 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();
            }
        }
    }
  27. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  28. Set the Name to LoansAllocations
  29. Click Add
  30. Design the form as follows:
     
    Watts A Loan - Loans Allocations
    Control (Name) Anchor Text
    DataGridView Data Grid View dgvLoansAllocations Top, Bottom, Left, Right  
    Button Button btnNewLoanAllocation Bottom, Left New Loan Allocation...
    Button Button btnLoanAllocationEditor Bottom, Right Loan Allocation Editor...
    Button Button btnClose Bottom, Right Close
  31. Double-click an unoccupied area of the form
  32. Return to the form and double-click the New Loan Allocation button
  33. Return to the form and double-click the Loan Allocation Editor button
  34. Return to the form and double-clic the Close button
  35. 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();
            }
        }
    }
  36. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  37. Set the Name to NewLoanPayment
  38. Click Add
  39. Design the form as follows:
     
    Watts A Loan - New Payment
    Control Text Name Other Properties 
    Label Label Payment Date:    
    DateTimePicker Date Time Picker   dtPaymentDate  
    Label Label Payment Processed By   BackColor: Gray
    AutoSize: False
    Label Label Employee #:    
    MaskedTextBox Masked Text Box   txtEmployeeNumber Mask: 000-000
    TextBox Text Box   txtEmployeeName  
    Label Label Payment Processed For   BackColor: Gray
    AutoSize: False
    Label Label Loan Details   BackColor: Gray
    AutoSize: False
    Label Label Loan Number:    
    TextBox Text Box   txtLoanNumber  
    Label Label Payment Amount:    
    TextBox Text Box 0.00 txtPaymentAmount  
    TextBox Text Box   txtLoanDetails Multiline: True
    ScrollBars: Vertical
    Label Label Previous Balance:    
    TextBox Text Box   txtPreviousBalance  
    Label Label Current Balance:    
    TextBox Text Box 0.00 txtCurrentBalance  
    Label Label Notes    
    TextBox Text Box   txtNotes Multiline: True
    ScrollBars: Vertical
    Button Button Submit btnSubmit  
    Button Button Close btnClose  
  40. To create a new form, on the main menu, click PROJECT -> Add Windows Form...
  41. Set the Name to LoansPayments
  42. Click Add
  43. Design the form as follows:
     
    Watts A Loan - Loans Payments
    Control (Name) Text Anchor
    DataGridView Data Grid View dgvLoansPayments   Top, Bottom, Left, Right
    Button Button btnNewPayment New Payment... Bottom, Left
    Button Button btnClose Close Bottom, Right
  44. Double-click an unoccupied area of the form
  45. Return to the form and double-click the New Payment button
  46. Return to the form and double-click the Close button
  47. 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();
            }
        }
    }
  48. Access the WattsALoan form
  49. Design the form as follows:
     
    Watts A Loan
    Control (Name) Name
    Button Button btnCustomers Customers
    Button Button btnLoansAllocations Loans Allocations...
    Button Button btnEmployees Employees
    Button Button btnLoansPayments Loans Payments...
    Button Button Close btnClose
  50. Double-click the Customers button
  51. Implement its event as follows:
    private void btnCustomers_Click(object sender, EventArgs e)
    {
        Customers clients = new Customers();
        clients.Show();
    }
  52. Return to the WattsALoan form and double-click the Loans Allocations button
  53. Implement the event as follows:
    private void btnLoansAllocations_Click(object sender, EventArgs e)
    {
        LoansAllocations las = new LoansAllocations();
        las.Show();
    }
  54. Return to the form and double-click the Employees button
  55. Implement its event as follows:
    private void btEmployees_Click(object sender, EventArgs e)
    {
        Employees clerks = new Employees();
        clerks.Show();
    }
  56. Return to the form and double-click the Loans Payments button
  57. Implement the event as follows:
    private void btnLoansAllocations_Click(object sender, EventArgs e)
    {
        LoansPayments lps = new LoansPayments ();
        lps.Show();
    }
  58. Return to the form and double-click the Close button
  59. 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 LearningPractical Learning: Creating a Stored Procedure

  1. 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();
    }
  2. Execute the application
  3. Click OK on the message box
  4. 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 LearningPractical Learning: Executing an Argumentative Procedure

  1. Display the NewLoanPayment form
  2. Click the Employee Number text box and, in the Properties window, click the Events button
  3. 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();
                }
            }
        }
    }
  4. Display the NewLoanAllocation form
  5. Click the Employee Number text box and, in the Events section of the Properties window, double-click the Leave button
  6. 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();
        }
    }
  7. Return to the NewLoanAllocation form
  8. Click the Account Number text box
  9. In the Events section of the Properties window, double-click Leave
  10. 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();
        }
    }
  11. Return to the NewLoanAllocation form
  12. Double-click the Calculate button
  13. 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");
    }
  14. Display the Loan Allocation Editor form
  15. Double-click the Find button
  16. 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();
            }
        }
    }
  17. Return to the Loan Allocation Editor form
  18. Click the Employee # text box and, in the Events section of the Properties window, double-click the Leave button
  19. 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();
        }
    }
  20. Return to the Loan Allocation Editor form
  21. Click the Account # text box and, in the Events section of the Properties window, double-click Leave
  22. 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();
        }
    }
  23. Return to the Loan Allocation Editor form
  24. Double-click the Calculate button
  25. 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 LearningPractical Learning: Using a Stored Procedure With Many Parameters

  1. Display the WattsALoan form
  2. Double-click an unoccupied area of its body
  3. 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();
    }
  4. Execute the application
  5. Click OK on the message box
  6. Close the form and return to your programming environment
  7. In the Load event of the Watts A Loan form, delete CreateStoredProcedures2(); as follows:
    private void WattsALoan_Load(object sender, EventArgs e)
    {
        
    }
  8. Display the New Loan Allocation form
  9. 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();
    }
  10. Return to the New Loan Allocation form and double-click the Close button
  11. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  12. Display the LoanAllocationEditor form
  13. 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 = "";
        }
    }
  14. Return to the Loan Allocation Editor form and double-click the Close button
  15. Implement the event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  16. Display the New Loan Payment form
  17. 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();
    }
  18. Execute the application
  19. Click the Loans Allocations button
  20. 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.
  21. Click Calculate
     
    Watts A Loan - New Loan Allocation
  22. Click Submit
  23. 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  
  24. 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.
  25. 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 LearningPractical Learning: Speifying the Direction of Parameters

  1. Display the WattsALoan form
  2. Double-click an unoccupied area of its body
  3. 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();
    }
  4. Execute the application
  5. Click OK on the message box
  6. Close the form and return to your programming environment
  7. In the Load event, delete CreateStoredProcedure2();
  8. Display the New Loan Payment form and click the Loan Number text box
  9. 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");
    }
  10. Return to the form and double-click its Close button
  11. Implement its event as follows:
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  12. Execute the application
  13. Click the Loans Payments button
  14. 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  
  15. 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, FunctionX