CODEWITHSUNDEEP
sqlsql-serverstored-proceduresjoin
Eda
Eda

Reputation: 208

SQL Server : Stored Procedure Syntax mishap

This is what I have to exexcute CREATE PROCEDURE sp_SalesByYr 1. Parameter: OrderYear2. Display the Total sales for the Year by territory

AdventureWorks2012 is the database.

Here is my take: it results in an error code -

Must declare the scalar variable @Result

What is the remedy? (The datatype for the output should be Money rather than Integer)

CREATE PROC sp_SalesByYr
    @OrderYear DateTime
AS
BEGIN
    SET NOCOUNT ON;
    SET         (SELECT SUM(@SalesYTD) SalesByTy 
                FROM    Sales.SalesOrderHeader a
                WHERE   a.OrderDate = @OrderYear  
                GROUP BY b.TerritoryID 
                )
    END

DECLARE @Result Money
EXEC    sp_SalesByYr '2002' OUTPUT 

PRINT @Result

Upvotes: 1

Views: 4016

Answers (2)

Eda
Eda

Reputation: 208

CREATE PROC sp_SalesByYr @OrderYear INT AS BEGIN SET NOCOUNT ON; SET SELECT a.TerritoryID Territory, SUM (a.TotalDue) SalesByTy FROM Sales.SalesOrderHeader a WHERE DatePart(YY,a.OrderDate) = @OrderYear
GROUP BY a.TerritoryID 

END

EXEC sp_SalesByYr '2002' OUTPUT

Upvotes: 0

Jason W
Jason W

Reputation: 13209

You may just need a "GO" after the END statement. The CREATE PROC is including everything until the GO and therefore including your test statements.

I made a few more changes, but made a few assumptions - maybe it will help.

  • Change SUM(@SalesYTD) to SUM(TotalDue) so you calculate the sales for the year in the paramter
  • Removed the OUTPUT from the territory assuming you're calculating trying to query by territory
  • Changed the year being queried to 2006 instead of 2002 since there are no sales in 2002 in AdventureWorks
  • Changed the WHERE clause for the year to YEAR(OrderDate) = @OrderYear
  • Added to WHERE clause so territory is optionally filtered as well

Final code became:

CREATE PROC csp_SalesByYr
    @OrderYear DateTime, 
    @SalesYTD Money OUTPUT,
    @Territory Nvarchar(50) = NULL
AS
BEGIN
    SET NOCOUNT ON

    SET @SalesYTD = (
        SELECT SUM(TotalDue)
        FROM Sales.SalesOrderHeader SO
            INNER JOIN Sales.SalesTerritory T
                ON SO.TerritoryID = T.TerritoryID
        WHERE YEAR(SO.OrderDate) = @OrderYear
            AND (@Territory IS NULL OR T.Name = @Territory)
    ) 
END
GO
DECLARE @Result Money
EXEC    csp_SalesByYr 2006, @Result OUTPUT, 'Northeast'
PRINT  @Result

Upvotes: 1

Related Questions