Create Store Procedure TOAD Example

Create Store Procedure TOAD Example

In this example we will see how to create store procedure using TOAD. I assume you already set up your TOAD and connected to the database.

• First create table on TOAD. Below is script:

create table dbo.Test (
	test_id int identity not null,
	eventDate date null,
	createTime datetime default getdate() null,
	updateTime datetime null,
	primary key (test_id) 
)

• Insert some data to the Test table:

INSERT INTO OTC.dbo.Test(
  eventDate
  ,updateTime
) VALUES (
   '20151226' -- eventDate - IN date
   ,'12/26/2015 12:00:00 AM' -- updateTime - IN datetime
)

• Click Procedure tab on TOAD:

• Create new procedure icon to create new store procedure:

• To make this procedure simple we will execute select statement which will take one parameter test_id which is primary key of the table:

• Click Parameter to add new parameter to it:

• Now if you click show script button, it will show you final script of the store procedure:

• Finally click execute button to create store procedure:

Reference:

• Creating Stored Procedures