SQL Stored Procedure Example

For you guys over in Yakima who want to see some more SQL I thought I’d whip up a quick example of a pretty typical stored procedure that could have been used on my Digital Gamer Zone website. Here is the relevant diagram for the SQL I will be writing.

For this example I want to create a stored procedure that will return the ASIN and Title of all the games associated with a particular UserId. The ASIN is actually Amazon Standard Identification Number. If you have gone to http://digitalgamerzone.com/ , registered and gone to the “My Games” tab on you will find a huge library of games from which to drag and drop Into your personal Library. Now I sure as heck did not download those by hand. I created a small app that would download them for me after hitting an Amazon web service to find all the Xbox games that Amazon sells. If it’s good enough for Amazon, it’s good enough for me.

USE [MyGameSiteDB]
GO

SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER OFF
GO
Create PROCEDURE [dbo].[GetUserGames] (
@UserID NVARCHAR(36)
)
AS
BEGIN

Select Distinct g.ASIN,
g.Title
FROM MyGames mg
Join Games g on mg.ASIN = g.ASIN
Where
mg.userid=@UserId

End

This example is ridiculously simple but at least it’s real. Who want s to see another Select * from Employees example? Not me!

Also, though my score is currently low. I will pwn you on Halo 3! That’s right, I said it!