Store Procedure receiving an instance of a table variable

The store procedure GetSummarySales receive an instance of the intKeysTabletype and return summarized Sales dollars for 2007 for each Customer.



USE [BookSales]
GO

/****** Object: StoredProcedure [dbo].[getSummarySales] Script Date: 03/09/2012 17:46:07 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getSummarySales]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[getSummarySales]
GO

USE [BookSales]
GO

/****** Object: StoredProcedure [dbo].[getSummarySales] Script Date: 03/09/2012 17:46:07 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
-----Create the type table
Create Type IntKeysTableType as Table
(IntKey int not null)
go

---Create the store procedure that will receive the customer key as a parameter

CREATE PROCEDURE [dbo].[getSummarySales]


@CustomerKeyList IntKeysTableType READONLY


AS
BEGIN



;with totalCTE as

(
select c.CustomerName, Sum(fb.SalesDollars) as TotSales, c.CustomerPK

from FactBookSales as fb
join DimCustomers as c
on fb.CustomerPK=c.CustomerPK
join DimDates as d
on fb.DatePK=d.DatePK where d.Year in('2007')
group by c.CustomerPK, c.CustomerName
)

select CustomerName, TotSales
from totalCTE
join @CustomerKeyList CustomerKeylist
on CustomerKeylist.IntKey=totalCTE.CustomerPK
Order by CustomerPK


END

GO

---This query call the store procedure, passing the table type variable for customers 1,3,5,7 and 9

declare @CustomerKeyList IntKeysTableType
insert into @CustomerKeyList values(1),(3),(5),(7),(9)
exec dbo.getSummarySales @CustomerKeyList