Reputation: 968
So basically I'm trying to replicate this query as a stored procedure.
Use ThisDB
SELECT FirstName + ' ' + LastName as FullName ,
sum(UnitPrice * Quantity) as 'Total Sales',
YEAR(OrderDate) as SalesYear
From Employees e
Join Orders o on o.EmployeeID = e.EmployeeID
join [Order Details] od on od.OrderID = o.OrderID
Group by LastName +' ' +FirstName, YEAR(OrderDate)
Order by 'Total Sales' desc
Edit: I noticed that the old query was gonna fail, but that don't change too much /edit
Unfortunately, I haven't found any good examples that help me translate what I know about queries to stored procedure syntax. Here is what i understand so far:
Use ThisDB;
go
CREATE PROCEDURE empSalesByYear as @Emp OUT
BEGIN
Set Emp = (SELECT Employees.FirstName, Employees.LastName ,
TotalSales = sum([Order Details].UnitPrice * [OrderDetails].Quantity),
YEAR(orders.OrderDate) as SalesYear
From Employees e
Join Orders o on o.EmployeeID = e.EmployeeID
join [Order Details] od on od.OrderID = o.OrderID
group by FirstName , LastName
Order by TotalSales desc)
END
GO
Predictably, SQL server
give me the finger when I type all this in. . .
My goal here is a stored procedure that takes zero input parameters and outputs a table like the first query.
Thanks.
Upvotes: 1
Views: 99
Reputation: 42023
Further to my comment, you can just do:
CREATE PROCEDURE proc_GetEmpSalesByYear AS
SELECT FirstName + ' ' + LastName as FullName ,
sum(UnitPrice * Quantity) as 'Total Sales',
YEAR(OrderDate) as SalesYear
From ThisDB..Employees e
Join ThisDB..Orders o on o.EmployeeID = e.EmployeeID
join ThisDB..[Order Details] od on od.OrderID = o.OrderID
Group by LastName, FirstName Order by 'Total Sales' desc
GO
..unless you wanted to return a table output parameter?
(Removed the USE, missed that.. now using db..table format)
Upvotes: 1