Stored Procedures

So guys welcome to my SQL Stored Procedures small tutorial and i hope you’ll grab all what you want 🙂

So first we’ll see what is a Stored Procedure

Basically we write the same sql query in our system,programme in many places.The same code.Why we can’t use a template or something?? Yes that’s why we use STORED PROCEDURES 😛

Any more advantages??

  • You don’t need to compile it again and again.Just recompile it once you modified the procedure.
  • Performance improvement by executing the procedure on server.
  • Can provide security since user can execute stored procedure rather accessing tables.
  • If only the business logic changes then only change the stored procedure.

So i’ll show a example of a SQL query with the normal form first

SELECT Product, Quantity FROM Inventory
WHERE Warehouse = ‘Colombo'

And here is the stored procedure for the same code


CREATE PROCEDURE getLocation (@location varchar(10) )

AS

BEGIN
SELECT Product, Quantity FROM Inventory
WHERE Warehouse = @location
END;

Now you can call this simple procedure by Execute getLocation(‘Colombo’) or by

Exec getLocation(‘Colombo’)

Now simply you can use this procedure anytime you want by calling in the above way.

So i’ll explain the procedure above we wrote.

CREATE – Now you are suppose to create a procedure.So why we put any other?? Like wise we can use ALTER and DELETE words if we are updating the procedure or if we are deleting the procedures.

PROCEDURE – You can either call this as PROC.

getLocation(@location varcher(10)) – This is the procedure name and inside the brackets we should add all the parameters we need to take an outcome.The data type also should be there with the size.

AS – A key word.Just put it after declare the parameters.

BEGIN and END – We use these key words in order to identify the margins of each queries if we have many queries in the same procedure.These two is not a must but that can be a good practice when you go to apply these for bigger systems.

So i’ll show some sample codes for you 🙂

  • We can use this for insert data for a table.
CREATE PROC insertEmp ( @id integer,

@firstName varchar (10), @lastName varchar (20), @salary decimal (10,2) )

AS
INSERT INTO employee (id, firstName, lastName, salary) VALUES (@id, @firstName, @lastName, @salary) ;
  •   Can update some data in row.
CREATE PROC updateEmp ( @id integer,

@salary decimal (10,2) )

AS
UPDATE employee

SET salary = @salary WHERE id = @id;
  • Delete a row
CREATE PROC deleteEmp ( @id integer )

AS
BEGIN

DELETE FROM employee WHERE id = @id

END;
  • IF ELSE Statement
CREATE PROCEDURE Check_Availability (
@id int, @emp_name varchar (50), @address (30) )

AS BEGIN
IF NOT EXISTS (SELECT id FROM emp WHERE id = @id) BEGIN
INSERT INTO emp VALUES (@id, @emp_name, @address)

PRINT ‘New Record is Added’ END

ELSE BEGIN

PRINT ‘Employee is already existing’

END;
  • While loop
CREATE PROC getSupplierInfo 
AS 
BEGIN

DECLARE @rowmax INT, DECLARE @i INT 
DECLARE @ContactVARCHAR(100),
DECLARE @Company VARCHAR(50)
SET @rowmax = @@ROWCOUNT, 
SET @i = 1

WHILE (@i <= @rowmax) 
 BEGIN

  SELECT @Contact = Name, @Company = CompName 
  FROM CompanyInfo 
  WHERE RowID = @i
  PRINT (Contact: ' + @Contact + ' at ' + @Company )
  SET @i = @i + 1

 END

END;
  • Example – Calculate employee salary
CREATE PROCEDURE cal_salary (@id varchar, @salary float output) 
AS

BEGIN
declare @hrsWorked int
declare @rate decimal
declare @amt float

  SELECT @rate = e.rate, @hrsWorked = e.hrsWorked FROM emp e
  WHERE e.id = @id

  set @amt = @rate * @hrsWorked
  set @salary = @amt

END;

Now we’ll look how to update a Stored Procedure.For that we use the ALTER key word except the CREATE.Here i’ll share a example.Hope you’ll grab it easily 🙂


ALTER PROCEDURE getLocation @location varchar(10)
AS
BEGIN
  SELECT Product, Quantity
  FROM Inventory
  WHERE Warehouse LIKE @location + ‘%’

END;
  • Check the user login by the name and the password
CREATE PROCEDURE sp_IsValidLogon ( @UserName varchar(10), @Password varchar(15) )

BEGIN
IF EXISTS(SELECT *FROM User_Login WHERE UserName = @UserName AND Password = @Password)

Print ‘Valid Login’;

Else

Print ‘Invalid Login’;

END;
  • Change the procedure – Add failed logins to another table by the above procedure
ALTER PROCEDURE sp_IsValidLogon ( @UserName varchar(10), @Password varchar(15) )
AS

BEGIN
IF EXISTS(SELECT * FROM User_Login WHERE&nbsp;UserName = @UserName And Password = @Password)

PRINT ‘Valid’;

ELSE

INSERT INTO FailedLogins(UserName, Password) VALUES(@UserName, @Password)

END;

Then we’ll check how to delete a procedure using DELETE by an example.

DROP PROCEDURE 

Not a big deal.

So those are the main things of Stored Procedure and please let me know if you have any queries regarding this by commenting my post and please correct me if i have mistaken anything here.

Thank you!!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s