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
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 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.
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.