Search This Blog

Thursday, October 6, 2011

Stored Procedures in SQL-SERVER 2005/2008

Stored Procedures

 What Stored Procedure means

A Stored procedure is a database object that contains one or more SQL statements. In this article you will get an idea on how to create and use stored procedures and also highlighted on how to use stored procedure.

The first time a stored procedure is executed; each SQL statement it contains is compiled and executed to create an execution plan. Then procedure is stored in compiled form with in the database. For each subsequent execution, the SQL statements are executed without compilation, because they are precompiled. This makes the execution of stored procedure faster than the execution of an equivalent SQL script.

To execute a stored procedure you can use EXEC statement.
CREATE PROC spGetAuthors

When you run this script in Pubs database you will get the following message in Query Analyzer.
The Command(s) completed successfully.

Now you are ready to call/execute this procedure from Query Analyzer.
EXEC spGetAuthors 

This stored procedure creates a result set and returns to client.

You can call a stored procedure from within another stored procedure. You can even call a stored procedure from within itself. This technique is called a recursive call in programming. One of the advantages of using stored procedures is that application programmers and end users don’t need to know the structure of the database or how to code SQL. Another advantage of it is they can restrict and control access to a database.
Now days every one is familiar with SQL Injection Attack I think stored are the way this can be prevented from this malicious attack.

How to Create a Stored Procedure

When the CREATE PROCEDURE statement is executed, the syntax of the SQL statements within the procedure is checked. If you have made a coding error the system responds with an appropriate message and the procedure is not created.

The Syntax of the CREATE PROCEDURE statement
AS sql_statements

You can use CREATE PROCEDURE statement to create a stored procedure in the database. The name of the stored procedure can be up to 128 characters and is typically prefixed with the letters sp.
If you look at the above options like AS, RECOMPILE, ENCRYPTION these are having some significance meaning to it.
The AS clause contains the SQL statements to be executed by the stored procedure. Since a stored procedure must consist of single batch.
Recompile is used when you want to compile the stored procedure every time when you call. This comes into the picture when one doesn’t want to catch the execution plan of stored procedure in the database. Encryption implies that you want to hide this code so that no one can see it. This is very important when you want to distribute the code across the globe or when you want to sell this code to other vendors. But make sure you have original copy it; because once you encrypted it no one can decrypt it.

Apart from the stored procedure that store in the database a permanent entity you can create stored procedure as per you session. That means as long the as the session is alive then the stored procedure is available in the memory means in the database.
Once the session ends the stored procedure is vanished this actually depends on what type of stored procedure you have chosen to create it.

Stored procedure provide for two different types of parameters: input parameters and Output Parameters. An input Parameter is passed to the stored procedure from the calling program. An output parameter is returned to the calling program from the stored procedure. You can identify an output parameter with the OUTPUT keyword. If this keyword is omitted the parameter is assumed to be an input parameter.
You can declare an input parameter so that it requires a value or so that its value is optional. The value of a required parameter must be passed to the stored procedure from the calling program on an error occurs. The value of an optional parameter doesn’t need to be passed from the calling program. You identify an optional parameter by assigning a default value to it. Then if a value isn’t passed from the calling program, the default value is used. You can also use output parameter as input parameters. That is you can pass a value from the calling program to the stored procedure through an output parameter. However is not advisable to pass parameters to Output parameters.

The syntax for declaring the parameters
@Parameter_name_1 data_type [= default] [OUTPUT]
[, @Parameter_name_2 data_type [= default] [OUTPUT]…

Parameter declarations
@FirstName varchar(50) -- Input parameter that accepts a string.
@LastName varchar(50) -- Output Parameter that returns a string.
Create Procedure statement that uses an input and an output parameter.
CREATE PROC spGetAuthors
                       @FirstName varchar(50),
                       @LastName varchar(50)
SELECT  @LastName= ln_Name 
WHERE   fn_name = @FirstName

Create procedure statement that uses an optional parameter.
CREATE PROC spGetAuthors
                       @LastName varchar(50),
                       @FirstName varchar(50) = ‘Rashmi Kant’
SELECT  @LastName= ln_Name 
WHERE   fn_name = @FirstName

A stored procedure can declare up to 2100 parameters. If you declare two or more parameters, the declarations must be separated by commas.

Calling stored procedure with Parameters

To pass parameter values to a stored procedure, you code the values in the EXEC statement after the procedure name. You can pass the parameters either by position or by name.

Passing parameters by Name:

Write the following code in Query Analyzer
EXEC spGetAuthors @FirstName = ‘Rashmi Kant’, @LastName = @LN OUTPUT

Passing parameters by Position:
EXEC spGetAuthors @LN OUTPUT, ‘Rashmi Kant’

In fact you can use both notations to pass parameters to stored procedures when you are calling. To pass parameters by position, list them in the same order as they appear in the CREATE PROCEDURE statement and separate them with commas. When you use this technique, you can omit optional parameters only if they are declared after any required parameters.

To use an output parameter in the calling program, you must declare a variable to store its value. Then you use the name of the variable in the EXEC statement and you code the OUTPUT keyword after it to identify it as an output parameter.

Handling error in stored procedure

In addition to passing output parameters back to the calling program, stored procedures also pass back a return value. By default, this value is zero. If an error occurs during the execution of a stored procedure you may want to pass a value back to the calling environment that indicates the error that occurred. To do that you use the RETURN statement and the @@ERROR function.

The @@ERROR system function returns the error number that’s generated by the execution of the most recent SQL statement. If the value is zero, it means that no error has occurred. The stored procedure listed below uses this function to test whether a DELETE statement that deletes a row from authors table is successful.
CREATE PROC spDeleteAuthors @FirstName varchar(50)
DECLARE @ErrorVar int
SET @ErrorVar = @ERROR
IF @ErrorVar <> 0 
               PRINT ‘An Unknown Error Occurred’
               RETURN @ErrorVar

RETURN statement immediately exists the procedure and returns an optional integer value to the calling environment. If you don’t specify the value in this statement the return value is zero.

How to delete or change a stored procedure

You use DROP PROC statement to delete one or more stored procedures from database. To redefine the stored procedure you use ALTER PROC.

The syntax of the DROP PROC statement
DROP {PROC|PROCEDURE} Procedure_name [, …]

The syntax of the ALTER PROC statement
AS sql_statements

When you delete a procedure any security permission that are assigned to the procedure are also deleted. In that case you will want to use the ALTER PROC statement to modify the procedure and preserve permissions.

Wednesday, October 5, 2011



In this article, we will consider the following controls in

·         RequiredField Validator,
·         Compare Validator
·         RangeValidator, and
·         Validation Summary

RequiredField Validator is used to inform the user if he leaves any of the data field(textbox , checkbox, dropdown ,password etc.) blank and submits the form.
CompareValidator compares data submitted in a field with pre-set value and report an error in case of a mis- match.

Range Validator checks if the value entered by the user falls within the specified range. If not, an error is reported.

Validation summary summarizes all the error messages and displays them  in a group. It is not a validation control as such but a reporter of error messages.

In this article example, the user leaves text blank, types 3000 in text 2 and types.
‘American’ in  text3. We get three error messages displayed  by the side of the respective input texts and also a summary report at the end of the form. A screen shot has been given.

Top of Form

a) Requiredfeildvalidaror
b) Rangevalidator(5000 and 10000)
c) comparevalidator
Reqired Feild validator
name? you must enter a user name
Range validator
Enter your salary ? you must enter a valid number
enter your nationality [indian?]
dose not match
validation summary
  • you must enter a user name
  • you must enter a valid number
  • dose not match

Bottom of Form

HTML    View
                <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">

<html xmlns="">
<head runat="server">
    <title>Untitled Pagetitle>
    <form id="form1" runat="server">
a) Requiredfeildvalidaror<br>
b) Rangevalidator(5000 and 10000)<br>
c) comparevalidator<br>
   Reqired Feild validator <br>
  name? <asp:TextBox ID="text1" runat="server" >asp:TextBox>
  <asp:RequiredFieldValidator ID="rfv1" ErrorMessage="you must enter a user name" ControlToValidate="text1" runat="server">asp:RequiredFieldValidator>
 _______________________ <br>
  Range validator <br>
   Enter your salary ?
   <asp:Textbox ID="text2" runat="server">asp:Textbox>
   id ="rv1"
   ErrorMessage = "you must enter a valid number"
   ControlToValidate= "text2"
   Minimumvalue ="5000"
   Maximumvalue ="10000"
   Type ="Integer" runat ="server">asp:Rangevalidator><br>
   comparevalidaror <br>
   enter your nationality [indian?]<br>
    <asp:TextBox ID ="text3" runat="server">asp:TextBox>
    <asp:CompareValidator ID= "hjk" ErrorMessage = "dose not match" ValueToCompare ="indian" runat ="server" ControlToValidate="text3">asp:CompareValidator>
   <asp:Button OnClick ="job1" Text ="click" runat ="server" />
   validation summary <br>
   <asp:validationsummary ID ="gh" runat ="server" />

 Code behind part
protected void job1(object sender, EventArgs e)
        if (IsValid)
    } using a form to insert data into an sql server Database table

  creating  Simple Form in inserting data into  Database Table in SQl server

STEP1: Creating the Table in Database.
Create table in SQL using Mangment studio and Solution Explorer , Expand the database Right click on table -> click on new  table , create your column as per your requirement like the following.


Note: in this article, The Id is set to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.

Then after adding all the necessary fields, name your Table the way you like. 
STEP2: Setting up the UI.
  Create a web form like following. 


<html xmlns="">
<head runat="server">
    <title>Sample Registration Pagetitle>
    <style type="text/css">
            width: 100%;
    <form id="form1" runat="server">
        <table class="style1">
                    Full Name:td>
                    <asp:TextBox ID="TxtName runat="server">asp:TextBox>
                    <asp:TextBox ID="TxtUserName" runat="server">asp:TextBox>
                    <asp:TextBox ID="TxtPassword" runat="server" TextMode="Password">asp:TextBox>
                    Re Password:td>
                    <asp:TextBox ID="TxtRePassword" runat="server" TextMode="Password">asp:TextBox>
                    <asp:TextBox ID="TxtAddress" runat="server">asp:TextBox>
                    <asp:TextBox ID="TxtAge" runat="server">asp:TextBox>
                <asp:DropDownList ID="DropDownList1" runat="server" AppendDataBoundItems="true">
                <asp:ListItem Value="-1">Selectasp:ListItem>
    <asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />

As you can see, the UI is very simple. Now let’s set up the connection string.

STEP3: Setting up the Connection String

In your web.config file set up the connection string there as shown below:

    <add name="MyConsString" connectionString="Data Source=localhost;Initial Catalog=MyDatabase;Uid=sa;pwd=sa@123"
   providerName="System.Data.SqlClient" />

 MyConsString is the name of the Connection String that we can use as a reference in our codes for setting the connection string later.

STEP4: Calling up the ConnectionString in our codes

Here’s the method for calling the connection string that was set up in the web.config file.

  public string GetConnectionString()
        //sets the connection string from your web config file "ConnString" is the name of your Connection String
        return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
Public Function GetConnectionString() As String
        'sets the connection string from your web config file "ConnString" is the name of your Connection String
        Return System.Configuration.ConfigurationManager.ConnectionStrings("MyConsString").ConnectionString
End Function

STEP5: Writing the method for inserting the data from the registration page to the database.

In this Article, we are using the ADO.NET objects for manipulating the data from the page to the database.
Here’s the code block for inserting the data to the database.

private void ExecuteInsert(string name, string username, string password, string gender, string age, string address)
        SqlConnection conn = new SqlConnection(GetConnectionString());

        string sql = "INSERT INTO tblRegistration (Name, UserName, Password, Gender, Age, Address) VALUES "
                    + " (@Name,@UserName,@Password,@Gender,@Age,@Address)";
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlParameter[] param = new SqlParameter[6];

            //param[0] = new SqlParameter("@id", SqlDbType.Int, 20);
            param[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
            param[1] = new SqlParameter("@UserName", SqlDbType.VarChar, 50);
            param[2] = new SqlParameter("@Password", SqlDbType.VarChar, 50);
            param[3] = new SqlParameter("@Gender", SqlDbType.Char, 10);
            param[4] = new SqlParameter("@Age", SqlDbType.Int, 100);
            param[5] = new SqlParameter("@Address", SqlDbType.VarChar, 50);

            param[0].Value = name;
            param[1].Value = username;
            param[2].Value = password;
            param[3].Value = gender;
            param[4].Value = age;
            param[5].Value = address;

            for (int i = 0; i < param.Length; i++)

            cmd.CommandType = CommandType.Text;
        catch (System.Data.SqlClient.SqlException ex)
            string msg = "Insert Error:";
            msg += ex.Message;
            throw new Exception(msg);


Private  Sub ExecuteInsert(ByVal name As String, ByVal username As String, ByVal password As String, ByVal gender As String, ByVal age As String, ByVal address As String)
        Dim conn As SqlConnection =  New SqlConnection(GetConnectionString()) 

        String sql = "INSERT INTO tblRegistration (Name, UserName, Password, Gender, Age, Address) VALUES "
                    Dim " (@Name,@UserName,@Password,@Gender,@Age,@Address)" As +
            Dim cmd As SqlCommand =  New SqlCommand(sql,conn) 
            Dim param() As SqlParameter =  New SqlParameter(6) {} 

            'param[0] = new SqlParameter("@id", SqlDbType.Int, 20);
            param(0) = New SqlParameter("@Name", SqlDbType.VarChar, 50)
            param(1) = New SqlParameter("@UserName", SqlDbType.VarChar, 50)
            param(2) = New SqlParameter("@Password", SqlDbType.VarChar, 50)
            param(3) = New SqlParameter("@Gender", SqlDbType.Char, 10)
            param(4) = New SqlParameter("@Age", SqlDbType.Int, 100)
            param(5) = New SqlParameter("@Address", SqlDbType.VarChar, 50)

            param(0).Value = name
            param(1).Value = username
            param(2).Value = password
            param(3).Value = gender
            param(4).Value = age
            param(5).Value = address

            Dim i As Integer
            For  i = 0 To  param.Length- 1  Step  i + 1

            cmd.CommandType = CommandType.Text
        Catch ex As System.Data.SqlClient.SqlException
            Dim msg As String =  "Insert Error:" 
            msg += ex.Message
            Throw New Exception(msg)

        End Try
End Sub

STEP6: Calling the method ExecuteInsert()

You can call the method above at Button_Click event for saving the data to the database. Here’s the code block below:

 protected void Button1_Click(object sender, EventArgs e)
        if (TxtPassword.Text == TxtRePassword.Text)
            //call the method to execute insert to the database
            ExecuteInsert(TxtName.Text, TxtUserName.Text, TxtPassword.Text, DropDownList1.SelectedItem.Text, TxtAge.Text, TxtAddress.Text);

            Response.Write("Record was successfully added!");

            Response.Write("Password did not match");

Protected  Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs)

        If TxtPassword.Text = TxtRePassword.Text Then

            'call the method to execute insert to the database

            ExecuteInsert(TxtName.Text, TxtUserName.Text, TxtPassword.Text, DropDownList1.SelectedItem.Text, TxtAge.Text, TxtAddress.Text)

            Response.Write("Record was successfully added!")



            Response.Write("Password did not match")


        End If

End Sub

As you can see from the above code block, we check the value of the TxtPassword and TxtRePassword to see if match. If it match then call the method ExecuteInsert else display the error message stating that the “Password did not match”.

You also noticed that we call the method ClearControls for clearing the Text fields in the page. See the code block below for the ClearControls method:

public static void ClearControls(Control Parent)
        if (Parent is TextBox)
        { (Parent as TextBox).Text = string.Empty; }
            foreach (Control c in Parent.Controls)
Public Shared  Sub ClearControls(ByVal Parent As Control)

        If TypeOf Parent Is TextBox Then
          (Parent as TextBox).Text = String.Empty

            Dim c As Control
            For Each c In Parent.Controls


        End If

 End Sub