First Stored Procedure Example

Variable

Here is the work (mess) we made to show how to use variables

Use Automart

--these are the functions and queries we ended with last week
Select VehicleServiceID, ServiceName, ServicePrice, DiscountPercent, 
dbo.fx_Percentages(ServicePrice, DiscountPercent) as [Discount Price],
dbo.fx_GetPercent(ServicePrice, TaxPercent) as Tax
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=3

Select VehicleServiceID, 
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent)) as SubTotal,
Sum(dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Tax,
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent) + 
 dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Total
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=3
Group by VehicleServiceID

--this breaks up the above and divides it into variables
--it is a bit of a mess and much more work than the other way
--but does show the use of variables

--declare the variables
Declare @Subtotal decimal(10,2)
Declare @Tax decimal(10,2)
Declare @Total decimal(10,2)

--get the subtotal
Select @Subtotal=Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent))
From Employee.VehicleServiceDetail sd
Inner Join Customer.AutoService a
on sd.AutoServiceID=a.AutoServiceID
Where VehicleServiceID=3
group by VehicleServiceID

--get the tax amount
Select @Tax=Sum(dbo.fx_GetPercent(ServicePrice, TaxPercent))
From Employee.VehicleServiceDetail sd
Inner Join Customer.AutoService a
on sd.AutoServiceID=a.AutoServiceID
Where VehicleServiceID=3
Group by vehicleServiceID

--get total
print 'subtotal ' + Cast(@Subtotal as nvarchar)
print 'Tax ' + Cast(@Tax as nvarchar)
Select @Total=@subtotal + @Tax

Select VehicleServiceID, @Subtotal as SubTotal, @Tax as Tax, @total as Total
From Employee.VehicleServiceDetail
Where VehicleServiceID=3
Group by VehicleServiceID

---

Simple Stored Procedure

Here is our first, simple stored procedure

--here is a stored procedure that uses the functions
--and queries above and creates a parameter for the 
--VehicleServiceID. This gives it the flexibility
--so you can use it with any Vehicle's service
Go
Create proc usp_GetReceipt
@VehicleServiceID int
AS

Select VehicleServiceID, ServiceName, ServicePrice, DiscountPercent, 
dbo.fx_Percentages(ServicePrice, DiscountPercent) as [Discount Price],
dbo.fx_GetPercent(ServicePrice, TaxPercent) as Tax
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=@vehicleServiceID

Select VehicleServiceID, 
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent)) as SubTotal,
Sum(dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Tax,
Sum(dbo.fx_Percentages(ServicePrice, DiscountPercent) + 
 dbo.fx_GetPercent(ServicePrice, TaxPercent)) as Total
From Customer.AutoService a
inner join Employee.VehicleServiceDetail vsd
on a.AutoServiceID=vsd.AutoServiceID
Where VehicleServiceID=@VehicleServiceID
Group by VehicleServiceID

Go
--this is not part of the stored procedure. 
--these are using the stored procedure
--two versions with the same result
Execute usp_GetReceipt 4
Exec usp_GetReceipt @VehicleServiceID=7