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.
Syntax:
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.
Example:
EXEC procedure_name ; Deleting stored procedures
To delete stored procedures , use DROP keyword with procedure name and execute the command.
Example:
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;EXAMPLE:
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(‘ , ‘).
EXAMPLE:
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.
SOURCES : Microsoft T-SQL Documentation on Stored Procedures
READ MORE:
SQL BASICS
SQL Window Functions
