CODEWITHSUNDEEP
sql-serverstored-procedures
surfasb
surfasb

Reputation: 968

Apparently, Stored Procedure syntax is harder to understand than I anticipated

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

Answers (1)

Kieren Johnstone
Kieren Johnstone

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

Related Questions