Introduction
Stored procedures (sprocs) are generally an ordered series of Transact-SQL statements bundled into a single logical unit. They allow for variables and parameters, as well as selection and looping constructs. A key point is that sprocs are stored in the database rather than in a separate file.
Advantages over simply sending individual statements to the server include:
- Referred to using short names rather than a long string of text; therefore, less network traffiic is required to run the code within the sproc.
- Pre-optimized and precompiled, so they save an incremental amount of time with each sproc call/execution.
- Encapsulate a process for added security or to simply hide the complexity of the database.
- Can be called from other sprocs, making them reusable and reducing code size.
Parameterization
A stored procedure gives us some procedural capability, and also gives us a performance boost by using mainly two types of parameters:
- Input parameters
- Output parameters
From outside the sproc, parameters can be passed in either by position or reference.
Declaring Parameters
- The name
- The datatype
- The default value
- The direction
The syntax is :
@parameter_name [AS] datatype [= default|NULL] [VARYING] [OUTPUT|OUT]
Let's now create a stored procedure named "Submitrecord".
First open Microsoft SQL Server -> Enterprise Manager, then navigate to the database in which you want to create the stored procedure and select New Stored Procedure.
![@1.gif]()
See the below Stored Procedure Properties for what to enter, then click OK.
![2.gif]()
Now create an application named Store Procedure in .net to use the above sprocs.
Stored Procedure.aspx page code
- <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
- <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1transitional.dtd">
- <html xmlns="http://www.w3.org/1999/xhtml">
-
- <head runat="server">
- <title>Store Procedure</title>
- </head>
-
- <body>
- <form id="form1" runat="server">
- <div>
- <asp:Label ID="Label1" runat="server" Text="ID"></asp:Label>
- <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /><br />
- <asp:Label ID="Label2" runat="server" Text="Password"></asp:Label>
- <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /><br />
- <asp:Label ID="Label3" runat="server" Text="Confirm Password"></asp:Label>
- <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /><br />
- <asp:Label ID="Label4" runat="server" Text="Email ID"></asp:Label>
- <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /><br /><br />
- <asp:Button ID="Button1" runat="server" Text="Submit Record" OnClick="Button1_Click" /> </div>
- </form>
- </body>
-
- </html>
Stored Procedure.aspx.cs page code
- using System;
- using System.Data;
- using System.Configuration;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- using System.Data.SqlClient;
- public partial class _Default: System.Web.UI.Page {
- DataSet ds = new DataSet();
- SqlConnection con;
- //Here we declare the parameter which we have to use in our application
- SqlCommand cmd = new SqlCommand();
- SqlParameter sp1 = new SqlParameter();
- SqlParameter sp2 = new SqlParameter();
- SqlParameter sp3 = new SqlParameter();
- SqlParameter sp4 = new SqlParameter();
- protected void Page_Load(object sender, EventArgs e) {}
- protected void Button1_Click(object sender, EventArgs e) {
- con = new SqlConnection("server=(local); database= gaurav;uid=sa;pwd=");
- cmd.Parameters.Add("@ID", SqlDbType.VarChar).Value = TextBox1.Text;
- cmd.Parameters.Add("@Password", SqlDbType.VarChar).Value = TextBox2.Text;
- cmd.Parameters.Add("@ConfirmPassword", SqlDbType.VarChar).Value = TextBox3.Text;
- cmd.Parameters.Add("@EmailID", SqlDbType.VarChar).Value = TextBox4.Text;
- cmd = new SqlCommand("submitrecord", con);
- cmd.CommandType = CommandType.StoredProcedure;
- con.Open();
- cmd.ExecuteNonQuery();
- con.Close();
- }
- }
When we run the application, the window will look like this:
![3.gif]()
After clicking the submit button the data is appended to the database as seen below in the SQL Server table record:
![4.gif]()