Stored Procedure – multiple business logical and tasks can be defined in a single Stored Procedure
Benefits of Stored Procedure
- Multiple business logic can be defined in single stored procedure, and all the specified users can have the access to use it.
- A process of business logic can be commented with the statement/query, which saves lots of time to create technical document.
- It helps to avoid the extra load on database/server, also, saves the time that different users writing the same business logic and duplicate query writing on the same database.
- if business logic has to be changed then needs to change only once for all.
Stored procedure can be written with the parameter, which allow a user to input the value at the time of execution.
Manipulation can be done multiple time within the procedure, just need to define within ( begin ……. end)
CREATE PROCEDURE DATAVIEW(@YEAR_FLAGE AS INT )
DECLARE @DM INT
SET @DM = @YEAR_FLAGE
SELECT A.S_NO , A.PRODUCT , B.TYPE from BRAND AS A
ON A.PRODUCT = B.PRODUCT
WHERE A.YEAR = @DM
Stored procedure is located under programmability