Stored Proceedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Proceedure

Author

Topic

kevin24
Starting Member

13 Posts

Posted - 2011-12-15 : 23:00:26

Hi,
I am new to SQl. I am using version 2008 RC2.
I am trying to follow an example stored proceedure
using the Northwind database.

I copy the example but I get many errors and have
been unsuccessful in correcting them.

The code is:-

CREATE PROCEDURE[dbo].[GetSalesByCategoryWithPaging]
    @startRowIndex int,
    @maximumRows INT,
    @SalesCategoryCnt INT OUTPUT
AS
BEGIN
SELECT @SalesCategoryCnt=(SELECT COUNT(*) FROM
        (SELECT C.CategoryName, ProductName
         FROM [Order Details] OD
         inner join Orders O on O.OrderID = OD.OrderID
         inner join Products P on P.ProductID = OD.ProductID
         inner join Categories C on C.CategoryID = P.CategoryID
    GROUP BY C.CategoryName, ProductName) as salesCategoryCnt );

    Declare @currRowIndex INT;
    set @currRowIndex = (@startRowIndex * @maximumRows) + 1;

    With SalesEntries as
       (SELECT ROW_NUMBER() OVER (ORDER BY C.CategoryName ASC) as Row,
                         C.CategoryName,
                         ProductName,
                         TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2),
                             OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
        FROM [Order Details] OD
        inner join Orders O on O.OrderID = OD.OrderID
        inner join Products P on P.ProductID = OD.ProductID
        inner join Categories C on C.CategoryID = P.CategoryID
        GROUP BY C.CategoryName, ProductName )

    Select * FROM SalesEntries
    WHERE Row between @currRowIndex and @currRowIndex+@maximumRows-1
    order by CategoryName asc
END
GO

The errors are:-

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 16
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@startRowIndex".
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ' '.

Thank you for your kind attention. Kevin

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 00:03:08

Hi,

Change the database compatibility level using following command.

For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100

For Ref:
http://blog.sqlauthority.com/2008/10/21/sql-server-fix-error-incorrect-syntax-near-you-may-need-to-set-the-compatibility-level-of-the-current-database-to-a-higher-value-to-enable-this-feature-see-help-for-the-stored-procedure-sp_db/

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

kevin24
Starting Member

13 Posts

Posted - 2011-12-16 : 02:19:20

Hi,

Changed the compatibility level to (100)

Still get error messages:-

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@startRowIndex".
Msg 319, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 32
Incorrect syntax near ' '.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2011-12-16 : 04:57:55

Hi,

Is there any SQL Server Express versions in your System?
May be it causes error.

Pls refer the below site.

Ref:
http://blog.webactivedirectory.com/2011/09/01/sql-server-2008-express-error-incorrect-syntax-near/

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

kevin24
Starting Member

13 Posts

Posted - 2011-12-16 : 22:51:00

Hi,

Removed SQLExpress and now is ok.

Many thanks your guidance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources