SQL Procedures

SQL Procedures:


Procedures:
Procedures are like functions.
Procedures are easy to execute and save your time.
you do not have to write whole the sql statement again and again.
You only have to call the procedure.

You must create the table before you can apply procedure
on the table. Here I am applying procedures on doctor table


General syntax for procedures:

create procedure Procedure_Name
(
  --Parameters
)
as
begin
--Your sql statements
End


Procedure for inserting records:

Create a procedure and specify number of Parameters that will be passed to procedure. Parameters are the columns of table in which you want to insert the record. Number of Parameters should be equal to number of columns of the table.

create procedure insertdoc

(
@Doctor_Id int,                                --Here goes your Parameters
@Doctor_name varchar(20),
@Fee int,
@Specialty varchar(20)
)

as

begin

insert into doctor values(@Doctor_Id,@Doctor_Name,@Fee,@Speciality)

end                        --Procedure ends here

To execute a procedure write following

exec  insertdoc 110,'kalim',10,'dental'

Procedure for Deleting records:

To delete a record using procedure
Here delete procedure is accepting only one argument because records are deleted on the basis of primary key

create procedure deleterow
(
@Doctor_Id int,
)
as
begin

delete from doctor where doctor_id=@Doctor_Id

end

To execute the deleterow
Pass one argument which is primary key for a record As I am passing 109 Now execute procedure

exec  deleterow 109

Procedure for Searching records:

Records can be searched by many conditions here I am searching on the basis of Doctor_id which is primary key.

create procedure searchrec
(
@Doctor_id int
)
As
begin
select * from doctor where doctor_id=@Doctor_id
end

exec  searchrec 109

Procedure for Updating records:

create procedure updaterec
(
@Doctor_Name varchar(20),
@Fee int,
@Specialty varchar(20),
@Doctor_Id int
)
as
begin
update doctor set
Doctor_Name=@Doctor_Name,
Fee=@Fee,
Specialty=@Specialty
where doctor_id=@Doctor_id
end

--Now execute the procedure
exec updaterec1 'Kally',15000,'dentist',110







Comments

Post a Comment

Popular posts from this blog

Using Progress Bar In C#

Image
Using Progress Bar in C#: Progress Bar is very commonly used control in many applications to display the progress of task being performed. Progress Bar is displayed when copying files, deleting files or installing some software. How to use Progress Bar: ·          Start New Windows Form Application. ·          Go to Toolbox. ·          Select Progress Bar and Drag it to your Form. To make Progress Bar working you need to know some properties of Progress Bar Progress Bar.Maximum: Defines how long it should run Progress Bar.Step: Defines how much it should increase current position of Progress Bar. Progress Bar.Style: How should it look like? There is also a very basic method which is used to run Progress Bar properly. Progress Bar.PerformStep() Let’s see an example. In this example we will copy files and folders from one location to another and show the progress bar wh...
Read more

Get elements by class name in javascript

To get elements using class name we use getElementsByClassName method available with document object. This method takes name of class an argument.  It returns array of elements because more than one element(s) can have same class. < input type ="button" value ="Get By Class" onclick =" GetByClassName() " /> < div id ="myDiv" class ="myClass">     This is a div </ div > < script type ="text/javascript">   function GetByClassName() {         var elements = document.getElementsByClassName( "myClass" );         if (elements.length > 0) {             alert(elements[0].innerHTML);         }     else       alert( "No matching element found" );    }   </ script >
Read more

Jquery serer side datatables in asp.net

In this post we will learn how we can implement jquery   server side datatables in asp.net mvc application. We need to add datatables library to our page, we can add references of libray from datatables <a href="https://datatables.net/" target="_blank">website</a>. We need to create an html table on which we will apply datatable. <table class="table table-striped table-bordered table-hover table-checkable order-column dataTable" id="tblInventoryItems">   <thead>     <tr>       <th>Item Name</th>       <th>Item Price</th>       <th>Item Quantity</th>       <th></th>     </tr>   </thead>   <tbody></tbody> </table> Then we need the code which will apply the datatable to above html table. Below is the code to apply datatable $("yourtableid").DataTable({         "p...
Read more