Stored procedures are sets of SQL code that can be saved to the database and can be called anytime the user wants to execute them. Most of the time stored procedures are SQL queries that are repeated daily so it is a time-saving manoeuvre to store them for future use.
CREATE PROCEDURE procedure_name AS BEGIN <SQL STATEMENTS> END;
After creating the stored procedures, user can execute them using the EXEC keyword and the procedure name.
EXEC procedure_name ;
Deleting stored procedures
To delete stored procedures , use DROP keyword with procedure name and execute the command.
DROP PROCEDURE procedure_name;
Stored procedures with parameters
Stored procedures can also be used with parameters.
To use parameters with Stored procedures , use ‘@’ before parameter/variable and specify their datatype.
CREATE PROCEDURE procedure_name @variable_name data-type AS BEGIN <SQL statements> END;
Stored Procedures with Single Parameter.
We will use the following demo table named ‘ Doctors’ .
CREATE PROCEDURE recc_doc @city varchar(40) AS BEGIN SELECT * FROM DOCTORS WHERE CITY = @CITY END;
After executing the above lines, user can check the stored procedure in Database> Programmability> Stored Procedures .
Now user can execute it and pass a parameter value with it.
EXEC RECC_DOC @city = 'Manipal' ;
Results show rows where city is ‘Manipal’.
Stored Procedures with Multiple Parameters
Users can define Stored procedures with multiple parameters. All parameters should be separated using comma(‘ , ‘).
CREATE PROCEDURE recc_doc_price @city varchar(40), @price int AS BEGIN SELECT * FROM DOCTORS WHERE CITY = @CITY AND consultation_fee < @price END;
After executing the above lines, user can check again newly created Stored Procedure in Database
Lets execute the newly created Stored Procedure and pass some values as parameters.
EXEC RECC_DOC_PRICE @CITY = 'Manipal', @PRICE = 1400;
Note: keep in mind SQL is not case sensitive.
As you can see our result is correct.
Thanks for reading.