Asp.Net C#: Stored Procedure

<div dir="ltr" style="text-align: left;" trbidi="on">
In database, you need to do Insert, Update and Delete. If we want to make
 a reliable and high performance system then these four operations must 
be implemented by stored procedures. Stored procedure also prevents Sql 
Injection attacks and reduce network traffic. 
 
<h2>
Insert Operation</h2>
We can insert records into the table(s) 
using stored procedure by passing data in input parameters. Below code 
is used to insert record in the table "Employee" using stored procedure 
 <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> CREATE TABLE Employee</li>
<li class="L1">(</li>
<li class="L2"> EmpID int primary key, Name varchar(50),</li>
<li class="L3"> Salary int,</li>
<li class="L4"> Address varchar(100)</li>
<li class="L5">) </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/success.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> Insert into Employee(EmpID,Name,Salary,Address) Values(1,'Mohan',16000,'Delhi')</li>
<li class="L1">Insert into Employee(EmpID,Name,Salary,Address) Values(2,'Asif',15000,'Delhi')</li>
<li class="L2">Insert into Employee(EmpID,Name,Salary,Address) Values(3,'Bhuvnesh',19000,'Noida')</li>
<li class="L3">--See table</li>
<li class="L4">SELECT * FROM Employee </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/iusptab.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> CREATE PROCEDURE usp_InsertEmployee</li>
<li class="L1">@flag bit output,-- return 0 for fail,1 for success</li>
<li class="L2">@EmpID int,</li>
<li class="L3">@Name varchar(50),</li>
<li class="L4">@Salary int,</li>
<li class="L5">@Address varchar(100)</li>
<li class="L6">AS</li>
<li class="L7">BEGIN</li>
<li class="L8"> BEGIN TRANSACTION </li>
<li class="L9"> BEGIN TRY </li>
<li class="L0">Insert into Employee(EmpID,Name,Salary,Address) Values(@EmpID,@Name,@Salary,@Address)</li>
<li class="L1"> set @flag=1;</li>
<li class="L2">IF @@TRANCOUNT > 0</li>
<li class="L3"> BEGIN commit TRANSACTION;</li>
<li class="L4"> END</li>
<li class="L5"> END TRY </li>
<li class="L6">BEGIN CATCH</li>
<li class="L7">IF @@TRANCOUNT > 0</li>
<li class="L8"> BEGIN rollback TRANSACTION;</li>
<li class="L9"> END</li>
<li class="L0"> set @flag=0;</li>
<li class="L1"> END CATCH</li>
<li class="L2">END </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/success.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> --Execute above created procedure to insert rows into table</li>
<li class="L1">Declare @flag bit</li>
<li class="L2">EXEC usp_InsertEmployee @flag output,1,'Deepak',14000,'Noida'</li>
<li class="L3">if @flag=1</li>
<li class="L4"> print 'Successfully inserted'</li>
<li class="L5">else</li>
<li class="L6"> print 'There is some error' </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/usp_insEmp1.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> --Execute above created procedure to insert rows into table</li>
<li class="L1">Declare @flag bit</li>
<li class="L2">EXEC usp_InsertEmployee @flag output,4,'Deepak',14000,'Noida'</li>
<li class="L3">if @flag=1</li>
<li class="L4"> print 'Successfully inserted'</li>
<li class="L5">else</li>
<li class="L6"> print 'There is some error' </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/usp_insEmp2.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> --now see modified table</li>
<li class="L1">Select * from Employee </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/usp_insEmp3.png" /> <h2>
Retrieve Operation</h2>
We
 can retrieve data from one or more tables/views with the help of join, 
using stored procedure. We can put multiple sql statements with in a 
single stored procedure. Below code is used to fetch data from a table 
"Employee" using stored procedure 
 <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> -- first we Insert data in the table</li>
<li class="L1">Insert into Employee(EmpID,Name,Salary,Address) Values(1,'Mohan',16000,'Delhi')</li>
<li class="L2">Insert into Employee(EmpID,Name,Salary,Address) Values(2,'Asif',15000,'Delhi')</li>
<li class="L3">Insert into Employee(EmpID,Name,Salary,Address) Values(3,'Bhuvnesh',19000,'Noida')</li>
<li class="L4">go</li>
<li class="L5">--Now we create a procedure to fetch data</li>
<li class="L6">CREATE PROCEDURE usp_SelectEmployee</li>
<li class="L7">As</li>
<li class="L8">Select * from Employee ORDER By EmpID </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/success.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> --Execute above created procedure to fetch data</li>
<li class="L1">exec usp_SelectEmployee </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/usp_selEmp.png" /> <h2>
Update Operation</h2>
We
 can update records of the table(s) using stored procedure by passing 
data in input parameters. Below code is used to update a table 
"Employee" using stored procedure 
 <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> CREATE PROCEDURE usp_UpdateEmployee</li>
<li class="L1">@flag bit output,-- return 0 for fail,1 for success</li>
<li class="L2">@EmpID int,</li>
<li class="L3">@Salary int,</li>
<li class="L4">@Address varchar(100)</li>
<li class="L5">AS</li>
<li class="L6">BEGIN</li>
<li class="L7"> BEGIN TRANSACTION </li>
<li class="L8"> BEGIN TRY</li>
<li class="L9"> Update Employee set Salary=@Salary, Address=@Address</li>
<li class="L0"> Where EmpID=@EmpID </li>
<li class="L1"> set @flag=1; </li>
<li class="L2">IF @@TRANCOUNT > 0</li>
<li class="L3"> BEGIN commit TRANSACTION;</li>
<li class="L4"> END</li>
<li class="L5"> END TRY</li>
<li class="L6"> BEGIN CATCH</li>
<li class="L7">IF @@TRANCOUNT > 0</li>
<li class="L8"> BEGIN rollback TRANSACTION; </li>
<li class="L9"> END</li>
<li class="L0"> set @flag=0;</li>
<li class="L1"> END CATCH</li>
<li class="L2"> END </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/success.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> --Execute above created procedure to update table</li>
<li class="L1">Declare @flag bit</li>
<li class="L2">EXEC usp_UpdateEmployee @flag output,1,22000,'Noida'</li>
<li class="L3">if @flag=1 print 'Successfully updated'</li>
<li class="L4">else</li>
<li class="L5"> print 'There is some error' </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/usp_updEmp1.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> --now see updated table</li>
<li class="L1">Select * from Employee </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/usp_updEmp2.png" /> <h2>
Delete Operation</h2>
We
 can delete records of the table(s) using stored procedure by passing 
data in input parameters. Below code is used to update a table 
"Employee" using stored procedure 
 <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> CREATE PROCEDURE usp_DeleteEmployee</li>
<li class="L1">@flag bit output,-- return 0 for fail,1 for success</li>
<li class="L2">@EmpID int</li>
<li class="L3">AS</li>
<li class="L4">BEGIN</li>
<li class="L5"> BEGIN TRANSACTION </li>
<li class="L6"> BEGIN TRY</li>
<li class="L7"> Delete from Employee Where EmpID=@EmpID set @flag=1; </li>
<li class="L8">IF @@TRANCOUNT > 0</li>
<li class="L9"> BEGIN commit TRANSACTION;</li>
<li class="L0"> END</li>
<li class="L1"> END TRY</li>
<li class="L2"> BEGIN CATCH</li>
<li class="L3">IF @@TRANCOUNT > 0</li>
<li class="L4"> BEGIN rollback TRANSACTION; </li>
<li class="L5"> END</li>
<li class="L6">set @flag=0; </li>
<li class="L7">END CATCH </li>
<li class="L8">END </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/success.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> --Execute above created procedure to delete rows from table</li>
<li class="L1">Declare @flag bit</li>
<li class="L2">EXEC usp_DeleteEmployee @flag output, 4</li>
<li class="L3">if @flag=1</li>
<li class="L4"> print 'Successfully deleted'</li>
<li class="L5">else</li>
<li class="L6"> print 'There is some error' </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/usp_delEmp1.png" /> <pre class="prettyprint lang-sql linenums"><ol class="linenums">
<li class="L0"> --now see modified table</li>
<li class="L1">Select * from Employee </li>
</ol>
</pre>
<img class="img" src="http://www.dotnet-tricks.com/Content/images/sqlserver/usp_delEmp2.png" /> <h4>
Note</h4>
<ol class="notelist">
<li>In stored procedure we use output parameter to return multiple values.</li>
<li>Generally
 we use output parameter in stored procedure to get status of the 
operation as I used above "@flag" output parameter to get operations 
status whether these are successfully executed or not.</li>
</ol>
<h5>
Summary</h5>
In
 this article I try to explain basic Insert, Retrieve, Update, Delete 
Operations using Stored Procedures. I hope after reading this article 
you will be know how to implement these operations using stored 
procedure. I would like to have feedback from my blog readers. Please 
post your feedback, question, or comments about this article. 
</div>

SQL Server Stored Procedures Insert Retrieve Update Delete Operations

I n database, you need to do Insert, Update and Delete. If we want to make a reliable and high performance system then these four operatio...