Function Vs Stored Procedure In SQL

Many times in interviews, one question is commonly asked, "What is the difference between stored procedure and function?". So, considering the above requirement, I have decided to write this blog to help beginners and job seekers who might want to know about it.

What is a SQL Stored Procedure?

A Stored Procedure is a group of logical SQL statements to perform a specific task, such as insert, select, update, and delete operations on a table, and so on, which are stored in a SQL database.

What is a SQL function ?

A Function is a logical group of SQL statements that perform an operation on the group of SQL statements and return a single value. It is also stored in a database.

Summary

I hope this blog is useful for all readers and that these differences are enough to impress the interviewer. If you have any suggestions, then please contact me.

Call Stored Procedure with Dapper

As you know day by day Dapper Micro ORM increasing popularity among developers because of its speed and less code. So in this article explains how to call stored procedure with Dapper.
Suppose you have following Model class

public class EmpModel
    {
       
       [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }
        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }
        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }

    }
Now consider following stored procedure we have

Create procedure [dbo].[AddNewEmpDetails]  
(  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Insert into Employee values(@Name,@City,@Address)  
End  
Now following is the function which is used to pass above stored procedure to Dapper

public void AddEmployee(EmpModel objEmp)
        {
            //Passing stored procedure using Dapper.NET
                connection();
                con.Execute("AddNewEmpDetails", objEmp, commandType: CommandType.StoredProcedure);
               
        }
In the above function

  •  Connection() is the method which contains the connection string .
  •  Con is the SqlConnection class object.
  • AddNewEmpDetails is the stored procedure.
  • ObjEmp is the object of model class
 Summary

How To Create Stored Peocedure In SQL Server

In this article we will learn how to create the stored procedure in SQL server .Before lets see definition of stored procedure

What is a Stored Procedure?
A Stored Procedure is a group of logical SQL statements to perform a specific task, such as insert, select, update and delete operations on a table and so on which is stored in a SQL database.
 . There are two ways to create procedure
  1. Using wizard
  2. Using create keyword
Using wizard

Follow the following steps to create stored procedure
  1. Login into your SQL Server management studio
  2. Expand Databases  option
  3. After expanding the databases option select the specific database in which you wants to create stored procedure .
  4. After expanding the specific database find sub folder Stored procedure under Programmability folder .
  5. Now right click on Stored procedure folder and click on New stored procedure as shown in below

After clicking on New stored procedure option then some default code will be generated ,modify that default code as your need and create procedure.

Using create keyword 

Use the following code to create stored procedure using create keyword

Create Procedure AddEmployee
(
 --variable  declareations   
@id int=null,                                   
@Fname Varchar (50)=null,                    
@MName Varchar (50)=null,                    
@Lname Varchar (50)=null  
)
as
Begin
Insert Into employee (FirstName,MName,LastName)values(@Fname,@MName,@Lname)
End  
Watch Video


Summary

I hope this article is useful for all readers..if you have any suggestion related to this article then please contact me.

Logging Exception To Database In ASP.NET

Monitoring the errors of a live application is very important to avoid any inconvenience. In C# to handle exceptions the try, catch and finally keywords are used, so in this article we will learn how to catch the error and log error details to the Database table so developers can fix it as soon as possible.
So let us start by creating the application.
Use the following procedure to create a web site:

Download Aspose : API TO Create and Convert Files
  1. "Start" - "All Programs" - "Microsoft Visual Studio 2010".
  2. "File" - "New" - "Web Site..." then select "C#" - "Empty Weh Site" (to avoid adding a master page).
  3. Provide the project a name such as "ExceptionLoggingToDatabase" or another as you wish and specify the location.
  4. Then right-click on Solution Explorer rhen select "Add New Item" - "Default.aspx" page.
  5. Drag and Drop one GridView to the Default.aspx page. Then the page will look as follows.
<%@ 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/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body bgcolor="silver">
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
</asp:GridView>
</form>
</body>
</html>
In the preceding source code, we have taken the one grid view and we try to assign a file as the data source for the grid view that is not available so it can generate the error file not found and we can log these error details to the database table.
Now create the table and provide a name as Tbl_ExceptionLoggingToDataBase or as you wish to store the exception details as:

 
/****** Object:  Table [dbo].[Tbl_ExceptionLoggingToDataBase]    Script Date: 15-04-2014 23:34:43 ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
SET ANSI_PADDING ON  
GO  
  
CREATE TABLE [dbo].[Tbl_ExceptionLoggingToDataBase](  
    [Logid] [bigint] IDENTITY(1,1) NOT NULL,  
    [ExceptionMsg] [varchar](100) NULL,  
    [ExceptionType] [varchar](100) NULL,  
    [ExceptionSource] [nvarchar](max) NULL,  
    [ExceptionURL] [varchar](100) NULL,  
    [Logdate] [datetime] NULL,  
 CONSTRAINT [PK_Tbl_ExceptionLoggingToDataBase] PRIMARY KEY CLUSTERED   
(  
    [Logid] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  
GO  
  
SET ANSI_PADDING OFF  
GO 
 
 The table will look as:
 
 Now create the Stored Procedure to log the exception into the database table as:

Create Procedure ExceptionLoggingToDataBase  
(  
@ExceptionMsg varchar(100)=null,  
@ExceptionType varchar(100)=null,  
@ExceptionSource nvarchar(max)=null,  
@ExceptionURL varchar(100)=null  
)  
as  
begin  
Insert into Tbl_ExceptionLoggingToDataBase  
(  
ExceptionMsg ,  
ExceptionType,   
ExceptionSource,  
ExceptionURL,  
Logdate  
)  
select  
@ExceptionMsg,  
@ExceptionType,  
@ExceptionSource,  
@ExceptionURL,  
getdate()  
End  
Now create the class named ExceptionLogging to log the error to the database table and write the following code as:

ExceptionLogging.cs
using System;  
using context = System.Web.HttpContext;  
using System.Configuration;  
using System.Data.SqlClient;  
using System.Data;  
  
/// <summary>  
/// Summary description for ExceptionLogging  
/// article by Vithal Wadje  
  
/// </summary>  
public static class ExceptionLogging  
{  
  
    private static String exepurl;  
    static SqlConnection con;  
    private static void connecttion()  
    {  
        string constr = ConfigurationManager.ConnectionStrings["CharpCorner"].ToString();  
        con = new SqlConnection(constr);  
        con.Open();  
    }  
    public static void SendExcepToDB(Exception exdb)  
    {  
  
        connecttion();  
        exepurl = context.Current.Request.Url.ToString();  
        SqlCommand com = new SqlCommand("ExceptionLoggingToDataBase", con);  
        com.CommandType = CommandType.StoredProcedure;  
        com.Parameters.AddWithValue("@ExceptionMsg", exdb.Message.ToString());  
        com.Parameters.AddWithValue("@ExceptionType", exdb.GetType().Name.ToString());  
        com.Parameters.AddWithValue("@ExceptionURL", exepurl);  
        com.Parameters.AddWithValue("@ExceptionSource", exdb.StackTrace.ToString());  
        com.ExecuteNonQuery();  
  
  
  
    }  
  
  
}  
In the code above we have created a SendExcepToDB method that accepts the Exception class reference object and we can call this method from the default.aspx.cs file.

Now open the default.aspx.cs page and write the following code to assign the data source to the grid view:

protected void Page_Load(object sender, EventArgs e)    
   {    
       try    
       {    
    
           DataSet ds = new DataSet();    
           ds.ReadXml(Server.MapPath("~/emp.xml"));    
           GridView1.DataSource = ds;    
           GridView1.DataBind();    
    
       }    
       catch (Exception ex)    
       {    
    
       ExceptionLogging.SendExcepToDB(ex);    
       Label1.Text = "Some Technical Error occurred,Please visit after some time";  
   
       }    
       
   }    

In the code above, we have used try and catch keywords to handle the exception, from the first in the try block we are trying to assign the emp.xml as the data source to the grid view that is not available and in the catch block we are calling the method SendExcepToDB of the class ExceptionLogging to log the error by ing the Exception class reference object.
Now run the application The following dummy message we will be shown to the user and the actual error will be logged to the database table as:
 
Now all the Exception details will be logged into the database table as:
 

We can see that in the preceding table all the Exception details are logged into the database with an application URL and page Name along with other details that helps developers to fix the error easily.
Notes

  • Make the changes in the web.config file depending on your server location.
Summary

I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.

Download Aspose : API To Create and Convert Files

Display Single String Multiple Times in SQL Server Without Loop

To display single string multiple times in SQL Server without Loop use following query

Select Replicate('Vithal Wadje',5)  

Summary 
I hope this code snippet is useful ,if you have any suggestion regarding this article then please contact me.

Get Second or N th Salary from Table in SQL Server

I have given a name to my blog as 'Get Second or Nth. Salary from Table in sql server' because any interview this question is always asked, also according to my personal experience I have faced many interview and the same question is asked in my all interviews so to help freshers i have decided to write this blog,so let us start..
Create a one table named employee as follows
emptable.png
Now insert the some record into the table that shown in below and 
selectfrom employee
The output will be look like as follows..

recordtable.png

Now let write a query to retrieve second highest salary from employee table 

select MAX (salary) as Salary from employee

where salary < 

select MAX (salary) as Salary from employee   )

run the above query the output will be as follows...

second.png
From the above example its clear that ,the above query displays the second highest salary from table ,in the above query the nested query is executed first which retrieves the highest salary and because of less than condition in where clause the first query  compares with second query and retrieves less than highest salary that is second.
Now, if interviewer asked to display 3 rd highest salary at that time i will write another nested query with less than where condition but what if interviewer asked any number of salary at that time if i write number of nested queries according salary then its become complicated and also interviewer does not like this ,so use following query to retrieve any number of salary from table.

Query for Displaying N th. salary

SELECT TOP 1 salary
FROM 
(
SELECT DISTINCT TOP N salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
In the above query just replace N with number that which no. of salary or number you wants to retrieve from table .suppose

To retrieve 2nd highest salary then query is
SELECT TOP 1 salary
FROM 
(
SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
The above query displays the second highest salary.
To display 3 rd Highest salary
SELECT TOP 1 salary
FROM 
(
SELECT DISTINCT TOP 3 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
The above query displays the third highest salary.

To display 4 th highest salary
SELECT TOP 1 salary
FROM 
(
SELECT DISTINCT TOP 4 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
The above query display's the fourth highest salary. Similarly you can display any number of salary as shown in above.
Summary
I hope this small blog is useful for all job seekers and freshers,if you have any suggestion then please contact me.

Replication and its Types

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.
Replication is the process whereby data is copied between databases on the same server or different servers connected by LANs, WANs, or the Internet. Microsoft SQL Server replication uses a publish, distributor and subscribe metaphor.

Types of Replication in Sql Server
  •  Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing.
  •  Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites.
  •   Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise.
Summary
In this small blog i have explained brief about the replication ,i hope this blog is useful for all readers ,if you have any suggestion thgen please contact me.

SQL DELETE Vs TRUNCATE command

Many times in interviews, the interviewer will ask about the difference between SQL DELETE and TRUNCATE commands, so to answer this question, I have decided to write this blog to assist job seekers and those who want to know the difference between the two commands.

SQL Truncate


Truncate will remove all the rows from a table but strucure , and there will be no data in the table, but table strucure will remain as it is, such as columns and rows after we run the truncate command.

The following are some keypoints.
  • Truncate is faster and uses fewer system and transaction log resources than Delete.
  • The data is removed by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
  • Truncate removes all rows from a table, but the table structure, including its columns, constraints, indexes, and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
  • You cannot use Truncate on a table referenced by a FOREIGN KEY constraint. Because Truncate table is not logged, it cannot activate a trigger.
  • Truncate cannot be rolled back.
  • Truncate is a DDL command.
  • Truncate resets the identity of the table.

SQL Delete

The Delete command removes the rows from a table based on the condition that we provide with a Where clause.
 
The following are some keypoints.

  • Delete removes rows one at a time and records an entry in the transaction log for each deleted row.
  • If you want to retain the identity counter, use DELETE instead. If you want to remove a table definition and its data, use the DROP TABLE statement.
  • Delete It can be used with or without a "where" clause.
  • Delete Activates Triggers.
  • Delete can be rolled back.
  • Delete is a DML command.
  • Delete does not reset the identity of the table
Note:

If the current session is not closed, delete and truncate can both be rolled back when surrounded by transactions. If truncate is written in the Query Editor surrounded by transactions and if the session is closed, it can not be rolled back, but delete can be rolled back.

Summary

I hope this small blog is helpful for all job seekers and other readers. If you have any suggestions, then please contact me.

How SQL Select Query Works

SQL Server performs a couple of internal steps before executing a query. The steps that interest us here are compilation and execution.
When SQL Server receives a query for execution, its execution plan should already be present in memory (the procedure cache); if not, SQL Server will have to compile the query before executing it.
The compilation process is divided into four parts:
1.       Parsing
2.       Normalization
3.       Compilation 
4.       Optimization  
  Parsing:
During this stage, SQL Server checks the query for Syntax errors and transforms it into a compiler-ready structure that it will use later to optimize the query. It does not check for object names or column names.
Normalization:
At this stage, SQL Server checks all references to objects in the query. This is where we typically get the “Object not found” message when an object referenced in the query is not found in the database. SQL Server also checks to see if a query makes sense. For example, we cannot execute a table or select from a stored procedure.
Bear in mind that while we can optimize select, insert, and update statements, there is no way to optimize if, while, and for operators.
Compilation:
This is where we start building the execution plan for the query we passed to SQL Server. First, we create a sequence tree. The sequence tree is normalized, again, which includes adding implicit conversions if necessary. Also during this phase, if the query is referencing views, a view definition is placed in the query. If a statement is a DML statement, a special object is created called the query graph. The query graph is the object on which the optimizer works to generate an optimized plan for the query. This is the compiled plan that is stored in the procedure cache for reuse.
Optimization:
SQL Server Optimizer is a cost-based optimizer, which means that it will come up with the cheapest execution plan available for each SQL statement. For each SQL statement to run we need to use resources like CPU, memory, hard disk, etc. The cheapest plan is the one that will use the least amount of resources to get the desired output. For optimizing DML statements, SQL Server will test different indexes and join orders to get the best plan for executing the query. Your index definition helps optimizer by reducing/minimizing resource usage. If the index has a high selectivity then it is most suitable for optimization. Because a complex query will take into account all indexes and joins, there can be many paths to take to execute the query. In such cases, determining the best path for optimization can take a long time. The longer this process takes, the higher the cost that is involved.
So first, a trivial plan is generated. This plan assumes that cost-based optimization is costly; if there is only one path for execution possible, there is no point optimizing the query. For example, when placing a simple insert statement into a table, there is no way that your indexes or join orders can increase optimization, so the trivial plan is used.
Summary
I hope this blog is useful all readers, if you have any suggestion then contact me.

Alter Table Statement in SQL Server

Sometimes there is a need to update the existing table in a SQL database such as to add, remove column of the existing table or change the size or type of data type of the table Column. I have explained the Alter Statement of SQL with examples to make it easy to understand. Let us start it with Basics.

What is Alter Statement in SQL?

The ALTER TABLE statement allows user to modify an existing table such as to add, modify or drop a column from an existing table.

To demonstrate the Alter statement, First create the table in SQL server database named employee as
 
CREATE TABLE employee
(
id int not null,
Name varchar (50) ,
city varchar(50) ,
department varchar (10),
CONSTRAINT employees_pk PRIMARY KEY (id)
)

Adding column's to a table

To add a single column to an existing table use the following SQL query.

ALTER TABLE employee
ADD salary varchar(50);


You can also add multiple columns to an existing table, The following is the query

Alter Table employee add 
(
Region varchar (50),
Email Varchar (50)
)
 

Modifying Column of an Existing Table

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE Employee
MODIFY salary varchar(100)


The above query will modify the size of salary column to 100.

Drop Column of an Existing Table

You can also drop the columns of a existing table, use the following query

ALTER TABLE employee
DROP COLUMN salary
 
The above query will drop the column salary from existing employee table.

Advantages and Disadvantages of views in Sql Server


What is view?

View is the simply subset of table which are stored logically in a database  means a view is a virtual table in the database whose contents are defined by a query. 

To the database user, the view appears just like a real table, with a set of named columns and rows of data. SQL creates the illusion of the view by giving the view a name like a table name and storing the definition of the view in the database.

Views are used for security purpose in databases, views  restricts the user from viewing certain column and rows means by using view we can apply the restriction on accessing the particular rows and columns for specific user. Views display only those data which are mentioned in the query, so it shows only data which is returned by the query that is defined at the time of creation of the View. 

Advantages of views

Security

Each user can be given permission to access the database only through a small set of views that contain the specific data the user is authorized to see, thus restricting the user's access to stored data

Query Simplicity

A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view.

Structural simplicity

Views can give a user a "personalized" view of the database structure, presenting the database as a set of virtual tables that make sense for that user.

Consistency
A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.

Data Integrity

If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.

Logical data independence.

 View can make the application and database tables to a certain extent independent. If there is no view, the application must be based on a table. With the view, the program can be established in view of above, to view the program with a database table to be separated.

Disadvantages of views

Performance

Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time.

Update restrictions

When a user tries to update rows of a view, the DBMS must translate the request into an update on rows of the underlying source tables. This is possible for simple views, but more complex views are often restricted to read-only.

Types of Stored Procedure in Sql Server

Many times in interview one question commonly used that is what is stored procedure and its types, so by considering above requirement i have decided to write this blog to help freshers and job seekers also who might be wants to know about it.
So let us see
Types of Stored Procedures
  •     User Defined Stored procedure: The user defined stored procedures are created by users and stored in the current database
  •     System Stored Procedure: The system stored procedure has names prefixed with sp_. Its manage SQL Server through administrative tasks. Which databases store system stored procedures are master and msdb database
  •     Temporary Stored procedures: The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection  terminates between client and server
  •     Remote Stored Procedures: The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission
  •     Extended Stored Procedures: These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_

Stored Procedures in SQL

Sometimes there is a need to insert, select, update and delete records from a table using a single Stored Procedure instead of creating separate Stored Procedures for each operation.
Suppose I have one .aspx web page in which I need a to insert, select, update and delete records. To do that instead of creating four Stored Procedures to perform these tasks I will create a single Stored Procedure to satisfy my requirements and I will access it in code behind according to the action performed by the end user on a button click.
I have written this article specially focusing on newcomers and anyone new to SQL Stored Procedures, so let us start with a basic introduction.
What is a Stored Procedure?
A Stored Procedure is a group of logical SQL statements to perform a specific task, such as insert, select, update and delete operations on a table and so on which is stored in a SQL database.
Types Of Stored  Procedures
The following are the types of Stored Procedures:
  • User Defined Stored procedure: The user defined stored procedures are created by users and stored in the current database
  • System Stored Procedure: The system stored procedure have names prefixed with sp_. Its manage SQL Server through administrative tasks. Which databases store system stored procedures are master and msdb database
  • Temporary Stored procedures: The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection terminates between client and server
  • Remote Stored Procedures: The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission
  • Extended Stored Procedures: These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_
Advantages Of Stored Procedure Stored Procedures have the following advantages:
  1. Can reduce network traffic and latency, boosting application performance.
  2. Execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
  3. Help promote code reuse.
  4. Can encapsulate logic. You can change stored procedure code without affecting clients.
  5. Provides better security to your data.
Note
We can pass up to 2100 parameters in one Stored Procedure.
Creating a Stored Procedure
Before creating a Stored Procedure, we will create one table named employee in the SQL database which looks as in the following image.
I have set the primary key on the id column for the Identy specification.

Now we have a table to perform these operations. Now let us start to create the Stored Procedure.
The Stored Procedure is created using the keyword "Create Procedure" followed by the procedure name. Let us create the Stored Prcedure named "EmpEntry" as given below.


create Procedure EmpEntry
(
 --variable  declareations
@Action Varchar (10),                             --to perform operation according to string passed to this varible such as Insert,update,delete,select    
@id int=null,                                   --id to perform specific task
@Fname Varchar (50)=null,                     -- for FirstName
@MName Varchar (50)=null,                    -- for MName
@Lname Varchar (50)=null                      -- for LastName

)
----------------------------------------------------------------------------------------------------
---exec EmpEntry @Action='delete' ,@Fname='S',@MName='R',@Lname='M',@id='13'  --added by vithal wadje on 07-05-2015 for Complie Mode contribution
----------------------------------------------------------------------------------------------------
as
Begin
  SET NOCOUNT ON;

If @Action='Insert'   --used to insert records
Begin
Insert Into
 employee (FirstName,MName,LastName)values(@Fname,@MName,@Lname)
End  

else if @Action='Select'   --used to Select records
Begin
select *from
 employee
end
else if
 @Action='Update'  --used to update records
Begin
 update employee set FirstName=@Fname,MName=@MName,LastName=@Lname where id=@id
 End
 Else If
 @Action='delete'  --used to delete records
 Begin
 delete from employee where id=@id
 end
 End


The comments in the Stored Procedure above clearly explain which block is used for which purpose, so I have briefly explained it again. I have used @Action variable and assigned the string to them and according to the parameter passed to the Stored Procedure the particular block will be executed because I have kept these blocks or conditions in nested if else if conditional statements.
"The most important thing is that I have assigned null to each variable to avoid the effect on the parameter passed to the Stored Procedure because we are passing a different number of parameters but not the same number of parameters to the Stored Procedure to perform these tasks."
Using the Stored Procedure
After creating this Stored Procedure, now let us use it.
To execute the Stored Procedure "EmpEntry" that we created we need to use the keyword exec followed by the procedure name and the parameter list. I have explained how to use it below.
  • Inserting the Records
    The following is a sample of inserting the records into the Employee table that we created with the EmpEntry procedure:
    exec EmpEntry @Action='Insert' ,@Fname='vithal',@MName='G',@Lname='Wadje'
    After running this query the records will be inserted into the table employee. To see the records inserted into the table run the following query:
    select * from employee 
    The output will be as shown in the following:

    Their are two records you have seen because I have executed the procedure two times.
  • Selecting Records From table 
    exec EmpEntry @Action='Select'
    The output will be as follows:
     
  • Updating Records of table
    exec EmpEntry @Action='Update' ,@Fname='Manish',@MName='Kapil',@Lname='Sawant',@id=2 After executing the above query the id number 2 record will be updated in the table.
    To see, run the query: select * from employee
    The output will be as shown in the following:
  • Deleting the Records from table


    exec EmpEntry @Action='delete' ,@id=2

    After executing the above query the id number 2 record will be deleted from the table.
    To see, run the query: select * from employee
    The output will be as shown in the following:

Summary I hope this article is useful for all readers. Read my next article to know how to use this Stored Procedure in an ASP.Net form in code behind on a button click I hope this article is useful for all readers.  

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode