Stored Procedure (Multi-Step) SQL

CREATE PROCEDURE PartsInventbyState

(@ PartInt, @BeginDate smalldatetime, @EndDate smalldatetime, @State char(2))

AS

IF @BeginDate < '2009-01-01'

RETURN -1;

--working table

CREATE TABLE @temp

(Invoice_No int);

--return a list of all Invoice_Nos that used the given part between the specified dates

INSERT INTO #temp

(JobTicketID);

Select inv.Invoice_No

FROM Invoice_No As inv

JOIN Invoice_NoDetail invd ON inv.Invoice_No = invd.Invoice_No

JOIN PartUsed As pu On invd.Invoice_No = pu.Invoice_No

AND invd.SKU = pu.SKU

AND pu.PartID = @PART

WHERE inv.StartDate BETWEEN @BeginDate AND @EndDate;

--Join the temporary table and return the vehicles owned by customers who live in a state

Select c.LastName, c.FirstName, v.VehicleYear, v.Make, v.Model,

FROM Customer AS c

JOIN Vehicle AS v ON c.CustomerID = v.CustomerID

JOIN Invoice_No As inv  ON c.CustomerID = inv.CustomerID

JOIN #temp ON inv.Invoice_No = #temp.Invoice_No

WHERE c.StateOrProvince = @State;

--remove the temporary table

Drop TABLE #temp;