T-SQL: Stored Procedures (SELECT), SELECT By ID, Select Products by Supplier ID

In this example we will pass an input parameter to determine the result of the query.  We will pass in supplier id to get all the products that are provided by the supplier.

Here is the code

Use Northwind
GO
CREATE PROCEDURE dbo.selProductsBySupplierID
@SupplierID int
AS
SELECT DISTINCT p.ProductID,
p.ProductName,
p.UnitPrice
FROM Products p
INNER JOIN Suppliers s ON
p.SupplierID = @SupplierID
GO

The @SupplierID is the input parameter that the stored procedure expects, you use it in the join portion of the statement to match the SupplierID in the Products table. The DISTINCT keyword means that the query will only return one row in the result if there are duplicate rows.
Type the following to execute the stored procedure with the supplier id of 1

EXEC dbo.selProductsBySupplierID 1

The result should look like this