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:

Comments

Post a Comment

Popular posts from this blog

Installing AdventureWorks Sample Databases from Microsoft

Image
1. Type in the following URL into your browser's address bar       http://msftdbprodsamples.codeplex.com/ 2.  Click on the "Download" button on page 3.  Click on the recommended download link 4.  Unizp the file you just downloaded 5.  Open the SQL Server Management Studio, then right click on "Databases" and then select "Restore Database" 6.  Select "Device" under "Source" 7.  Click on the "..." button, and the "Select" backup devices will appear, select "File" for "Backup media type" 8.  Click on the "Add" button, and select the "AdventureWorks2014.bak" file, then click "OK" 8.  Click "OK" on the "Select backup devices" screen 9.  Click "OK" on "Restore Database" window 10.  A message will pop up that says you have successfully restored the AdventureWorks2014 database 11.  The "AdventureWorks2014" database is now ...
Read more

SQL: GROUP BY And HAVING

Image
SQL GROUPING allows you to segregate data into groups so that you can work on it separately from the rest of the table records. Let's say you want to get the number of products in a category in the Northwind database Products table. You would write the following query: SELECT COUNT(*) NumberOfProductsByCategory FROM Products GROUP BY CategoryID The query above gives you the following results: The query gives you the number of products in each category, however it's not very useful. You don't really know what category the count is for in each record. You might want to try to change the query into something like this: SELECT CategoryID,COUNT(*) NumberOfProductsByCategory FROM Products GROUP BY CategoryID The above query is more useful the preceding one, however it only gives you the CategoryID number not the CategoryName in the Categories table. Being the perfectionist that you are you say to yourself, I can do better. "Yes, I can". I think that was a ca...
Read more

ASP.NET : Create an Empty Web Application Project in Visual Studio 2012

Image
The purpose of this blog is to show you the bare bones of ASP.NET.  Often times when you search for a tutorial on the web you see that the tutorials are way too complex or an overkill of what you are trying to do at that moment.  So the in this blog series I want to cut through all the fat and just show you how to get things done quickly. The best way to start an ASP.NET web application is to create an empty project.  In this tutorial I will show you how to create an empty web application project in Visual Studio 2012. You can download Visual Studio 2012 at http://www.microsoft.com/visualstudio/eng/downloads#d-2012-editions   you can get a 90 trial by registering with microsoft.com.  Another alternative is to download the free Visual Studio Express version at http://www.microsoft.com/visualstudio/eng/downloads#d-2012-express   Here are the steps to create an empty web application project in Visual Studio 2012 1. Open Visual Studio 2012 2. Click on "File", t...
Read more