Stored Procedure in SQL Server

Stored Procedure:

Set of precompiled T-SQL statements that are stored in the database server.

Stored procedures accepts parameters and return multiple values in the form of output parameters to the calling procedure .

Advantages:-

• They allow modular programming.
• They allow faster execution.
• They can reduce the traffic network
• It hides the raw data by allowing only stored procedures to get access to the data.

Disadvantages:-

• it is not best place to place a  complex logic in the stored Procedure.
• Queries are all grouped together, so it's harder to see the context of the application where they are being used. Analyzing the impact of a change is longer, and doing the change is longer as well.
• If your data has a changes, then you would  have to recompile a procedure. Because cached query plans are not optimal for current data.

Types of Stored Procedures:-

• User Defined Stored Procedures
• System Stored Procedures
• Parametrized Stored Procedures. 

User Defined Stored Procedure:-

A user stored procedure is any program that is stored and compiled within SQL Server (but not in the master database) and prefixed with sp_. User stored procedures can be categorized into three distinct types: 

• User stored procedures 
• Triggers, and
• User defined functions

System Defined Stored Procedures:-

System stored procedures are prefixed by sp_. Creating a stored procedure prefixed with sp_ and it stores in the master database will make it available to any database without the need to prefix the stored procedure with the name of the database. 

Parametrized Stored Procedures:- It explains, this type of stored procedures can contain input and output parameters.

Example to create a Parametrized Stored Procedure :-

Create Procedure UDP_GetCategoryNamesbyCategoryID

@CategoryId bigint

AS 

Begin

SELECT CategoryType, CategoryName FROM Category WHERE CategoryId  = @CategoryId 

END

GO

 Example to create a User Defined Stored Procedure :-

Create Procedure  UDP_GetProductNames

AS 

Begin

SELECT ProductName, Productdate, Productcost  FROM Product

END

GO

Example of System Defined Stored Procedures:- 

This type of Stored Procedure can get it from Master database in SQL Server.