T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5

In most of your projects you will have to work with stored procedures.  As a developer most of the time you only have to concern yourself with the basic stored procedures such as the SELECT, INSERT, UPDATE, and DELETE stored procedures.  If there's a DBA then you will probably be handed a stored procedure written by the database god.  But if you are the only developer in the five mile radius you might have to get your hands dirty and roll your own stored procedure.  In this tutorial we will be creating a select stored procedure.

Here is how

1.  Right click on the "Northwind" database and then select "New Query" in "Microsoft SQL Server Management Studio"

2.  A new query window will be open type in the following into the query window to create the SELECT stored procedure.

USE Northwind
GO
CREATE PROCEDURE dbo.ProductsSuppliers
AS
SELECT p.ProductID,
p.ProductName,
p.UnitPrice,
s.CompanyName AS Supplier
FROM Products p
INNER JOIN Suppliers s ON
p.SupplierID = s.SupplierID
GO

We've just created a stored procedure that will get the supplier for each product. This involves getting information from two different tables, therefore you need to use a join.  The INNER JOIN is like a WHERE and AND clause.  We displayed the CompanyName as Supplier.  That's it!

3.  Type

  EXEC dbo.ProductsSuppliers

4. Your result should look something like this

Blogs In the T-SQL Series:

• T-SQL: Stored Procedure (INSERT), INSERT A New Product In Northwind Part 1
• ASP.NET : Stored Procedures (INSERT), Insert a new Northwind Product Part 2
• T-SQL: Stored Procedures (DELETE), DELETE An Existing Northwind Product Part 3
• T-SQL: Stored Procedures (UPDATE), UPDATE An Existing Product In Northwind Part 4
• T-SQL: Stored Procedures (SELECT), SELECT Products and The Supplier Part 5
• ASP.NET: Calling Stored Procedure With A Parameter With SqlParameter Part 6
• ASP.NET: Get a Single Value From a Stored Procedure Part 7
• SQL Server: Granting Access Permissions to Stored Procedures For IIS Part 8