Creating User-defined Stored Procedure

Here's a step-by-step guide on how to create a simple stored procedure in MS-SQL Server 2019. We'll create a stored procedure that retrieves product information based on a provided product ID.
Methods
You can use either:

• SQL Server Management Studio (SSMS):Provides a GUI interface.
• Transact-SQL (T-SQL) Code:Text-based commands for direct execution.

Steps using SQL Server Management Studio (SSMS):

1. Connect to your Database:In SSMS, open Object Explorer, connect to your SQL Server instance, and expand the database where you want to store the procedure.
2. Create the Stored Procedure:
   • Right-click on the "Stored Procedures" folder.
   • Choose "New Stored Procedure..."
3. Template and Code: SSMS will generate a basic stored procedure template. Replace it with the following code:
   

   CREATE PROCEDURE GetProductInformation
      @ProductID INT  
   AS
      SELECT ProductName, UnitPrice, UnitsInStock
      FROM Products
      WHERE ProductID = @ProductID; 
   GO
   

4. Save and Execute:
   • Click the Save button (or File > Save). Give the procedure the name "GetProductInformation'.
   • Click the Execute button (or press F5). This compiles and saves your stored procedure.

Steps using Transact-SQL (T-SQL):

1. Open Query Editor:In SSMS, click on "New Query".
2. Write T-SQL Code:Paste the code from step 3 above into the query window.
3. Execute:Click the Execute button (or press F5).

Explanation

• CREATE PROCEDURE GetProductInformation:Declares a stored procedure named "GetProductInformation".
• @ProductID INT:Defines an input parameter called `@ProductID` of data type `INT`.
• AS:Marks the start of the procedure's instructions.
• SELECT...:The query selects `ProductName`, `UnitPrice`, and `UnitsInStock` from the `Products` table, filtered by the provided `ProductID`.
• GO:A batch separator for T-SQL.

To execute the stored procedure:

EXEC GetProductInformation 5;  -- Replace '5' with the actual ProductID 

In this lesson, you will practice creating a simple stored procedure. The stored procedure called usp_DeleteEmployee encapsulates the procedure of deleting an employee from the Employees table. The usp part of the stored procedure is just a prefix that I like to use indicating a user-defined stored procedure.
To code this function, we will create a stored procedure that accepts one parameter: the employee ID.
In addition, the stored procedure will be coded to remove the employee from the Employees table using the value of that parameter.
This code is demonstrated in the following SlideShow:

Executing this code will compile the stored procedure and store it on the server. Executing the code does not actually run the stored procedure, which would delete the employee from the Employees table.

You can extend the functionality of the stored procedure by testing to see if the value exists in the database first.
If it does not, an error will be returned. In the next lesson, I will discuss modifying stored procedures.

CREATE PROCEDURE usp_DeleteEmployee @EmployeeID int
AS
IF EXISTS (SELECT * FROM employees WHERE EmployeeID 
= @EmployeeID)

DELETE
FROM Employees
WHERE EmployeeID = @EmployeeID
ELSE
RAISERROR ('Employee ID does not exist', 16, 1)

Click the Exercise link below to practice creating stored procedures.
Creating Stored Procedures - Exercise