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*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Oct 17 '14 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, Oct 17 '14 at 15:36
What happens when you simply execute your sp without using any output variables? — Dan Bracuk, Oct 17 '14 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, Oct 17 '14 at 16:16
I am still working on it, and I am sure that I will get it right. Pointers may come from this site; from else-where OR form within, but they will definitely come. — Eda, Oct 17 '14 at 16:19
Check out [this question](http://stackoverflow.com/questions/2245691/how-to-assign-an-exec-result-to-a-sql-variable) — programmer43229, Oct 17 '14 at 16:29

Jason W · Answer 1 · 2014-10-19T01:12:14.413

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

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 — Eda, Oct 17 '14 at 20:14
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. — Jason W, Oct 18 '14 at 21:14
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, Oct 18 '14 at 22:42

score 0 · Accepted Answer · answered Oct 20 '14 at 10:08

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

SQL Server : Stored Procedure Syntax mishap

2 Answers2