SQL Stored Procedures
What Is A Stored Procedure?
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
Creating A Stored Procedure
To create a Stored Procedure first expand the programability section in the tree view then right-click on stored procedure then New Stored Procedure That will then bring up the area you can work with to create your procedure.
In this example we are going to create a simple Insert and Update procedure.
First we need to create the procedure and give it a name so we can call the procedure later on:
CREATE PROCEDURE procedure_name
For this we will call our procedure dbo.customer_u
CREATE PROCEDURE customer_u
Next we need to prefix our parameter names using the @ sign and specify the data types.
@ID int,
@Customer varchar(50),
@ContactName varchar(50),
@Address1 varchar(50),
@Town varchar(50),
@County varchar(50),
@PostCode varchar(50),
@ContactNumber varchar(50),
@Email varchar(50)
Now we can create our INSERT Statement.
AS
BEGIN
IF @ID=0
BEGIN
INSERT INTO dbo.tblCustomer([Customer],[ContactName],
[Address1],[Town],[County],[PostCode],[ContactNumber],[Email])
VALUES(@customer,@ContactName,@Address1,@Town,@County,@PostCode,@ContactNumber,@Email)
If you now click execute you should get a message saying
Command(s) completed successfully.
This means the procedure works. Next we will work on the UPDATE Procedure. But first because you have ran the procedure already you will have to drop the procedure before you can run it again
DROP PROCEDURE customer_u
Otherwise you will recieve this error the next time you run the procedure
Msg 2714, Level 16, State 3, Procedure customer_u, Line 24 There is already an object named 'customer_u' in the database.
Now we can start our UPDATE Procedure
ELSE
BEGIN
UPDATE dbo.tblCustomer
SET [Customer]=@Customer,
[ContactName]=@ContactName,
[Address1]=@Address1,
[Town]=@Town,
[County]=@County,
[PostCode]=@PostCode,
[ContactNumber]=@ContactNumber,
[Email]=@Email
WHERE ID=@ID
If we now CREATE the procedure again and run it you should get the Command(s) Completed Successfully message again
Ok Now we have done a INSERT and UPDATE we will now do a procedure to DELETE from a table.
So first we need to CREATE PROCEDURE again
CREATE PROCEDURE dbo.Customer_d
Next we need to again prefix our parameter names using the @ sign and specify the data types. But this time the only parameter we need to prefix is ID
@ID int
so now we build the DELETE procedure
AS
BEGIN
DELETE FROM dbo.tblCustomer WHERE ID=@ID
END
And thats it CLICK RUN
Written By LHTP Limited [http://www.learnhowtoprogram.com/]
Keywords:
SQL, Stored Procedures
You are not currently logged on.
You must be a registered member to comment on our resource pages.
It is free to register. You can register here Registration Form
