A Stored procedure is a group of one or more pre-compiled SQL statements, the pre-compiled statements can be reused over and over again. Stored procedure can have input or output or both parameters, so the stored procedure can act based on the parameter values that are passed.
Create Store Procedure Syntax,
CREATE PROCEDURE Procedure_Name
AS
-------------
-------------
sql statements
GO
Execute Procedure Syntax
EXEC Procedure_Name
Demo Database
TableName: StudentDetails
| ID |
Name |
RollNumber |
Address |
City |
ZipCode |
| 1 |
Kiran |
0555888 |
120,Hanover sq |
London |
0520 |
| 2 |
Balu |
0445254 |
Matederos 2312 |
Mexico |
0521 |
| 3 |
Ganesh |
0652452 |
Matederos 2312 |
Mexico |
0521 |
| 4 |
Hari |
0452345 |
25,Hanover sq |
London |
0520 |
| 5 |
Naveen |
0546245 |
25,Hanover sq |
London |
0520 |
Stored Procedure Example
The following statement to create store procedure name "GetAllStudents", that returns all the students in the table
CREATE PROCEDURE GetAllStudents
AS
BEGIN
SELCET * FROM StudentDetails
END
GO;
Execute the above created Stored procedure
EXEC GetAllStudents
Stored Procedure with single input parameter
The following statement is to create a stored procedure name "GetAllStudentsByCity", that returns particular students in the table based on the city value.
CREATE PROCEDURE GetAllStudentsByCity
@CITY VARCHAR(30)
AS
BEGIN
SELCET * FROM StudentDetails WHERE city=@CITY
END
GO;
Execute the above stored procedure by passing city value
EXEC GetAllStudentsByCity 'London'
The above store procedure returns all the students who belong to London city.
Stored Procedure with Multiple input parameter
The following statement is to create a stored procedure name "GetAllStudentsByCityANDZipCode" that returns particular students in the table based on the city value.
CREATE PROCEDURE GetAllStudentsByCityANDZipCode
(@CITY VARCHAR(30),@zipcode bigint)
AS
BEGIN
SELCET * FROM StudentDetails WHERE city=@CITY AND ZipCode=@zipcode
END
GO;
Execute the above stored procedure by passing city and zip code values.
EXEC GetAllStudentsByCityANDZipCode 'Mexico',0521
The above store procedure return all the students belongs to London city and zip code is 0521
Stored Procedure Example with output parameter
The following statement is to create store procedure name "GetZipCodeByRollNumber", which returns zipcode by student roll number.
CREATE PROCEDURE GetZipCodeByRollNumber
(@RollNumber VARCHAR(30),@zipcode BIGINT OUTPUT)
AS
BEGIN
SELCET @zipcode=ZipCode FROM StudentDetails WHERE RollNumber=@RollNumber
END
GO;
Execute the above store procedure by passing RollNumber value
DECLARE @ZIPCODE BIGINT
EXEC GetZipCodeByRollNumber 0555888,@ZIPCODE OUTPUT
The above store procedure returns the ZIPCode value of student RollNumber 0555888 and stores it into @ZIPCODE variable
We have shown how stored procedure creates and executes with input and output parameters.