SQL Server : Stored Procedure Syntax mishap

Question

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.

Sales.SalesOrderHeader table Sales
Sales.SalesTerritory table

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

Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Oct 17, 2014 at 15:31
Territory=Name i would think would fail, you would need 'Name' would you not? Additionally you have an output variable of Territory, but you're never assigning a value to Territory. what is the definition of proc_TSales? — Kritner
– Kritner, Commented Oct 17, 2014 at 15:36
What happens when you simply execute your sp without using any output variables? — Dan Bracuk
– Dan Bracuk, Commented Oct 17, 2014 at 16:06
EXACT same thing as with the "OUTPUT" attached. There is an error code after I run'print'- it says: Must declare the scalar variable "@catchSYTD". — Eda
– Eda, Commented Oct 17, 2014 at 16:16

Jason W · Accepted Answer · 2014-10-19 01:12:14Z

1

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

edited Oct 19, 2014 at 1:12

answered Oct 17, 2014 at 16:41

Jason W

13.2k3 gold badges35 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eda Over a year ago

Thank You, Jason Wallace. I have tried the code with 'GO' after 'END'. Now when I run the last part, it says that the subquery produced more than 1 value

Jason W Over a year ago

You are getting more than one item back in the query when assigning to @SalesYTD because of the group by clause. Remove your group by and everything should run fine.

Eda Over a year ago

Thank you, Jason. Without the "GROUP BY" clause, I ran the following: DECLARE @catchSYTD Money EXEC Sp_SaleByYr '2002', @Territory=Name,@SalesYTD=@catchSYTD OUTPUT The result was a blank window with no value

Eda · Accepted Answer · 2014-10-20 10:08:27Z

0

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

answered Oct 20, 2014 at 10:08

Eda

2081 gold badge4 silver badges17 bronze badges

Collectives™ on Stack Overflow

SQL Server : Stored Procedure Syntax mishap

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related