Saturday, February 14, 2015

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:

No comments:

Post a Comment