CODEWITHSUNDEEP
sqlstored-proceduressap-ase
mahonej
mahonej

Reputation: 1

create procedure in Sybase ASE. error all the time

I tried to create a procedure in Sybase ASE SQL. When I tried to execute it, an error pops up saying "invalid syntax near the keyword 'rows'". Why does that error appear?

create procedure dbo.sp_show_huge_tables
@top int=NULL, 
@include_system_tables bit=0

as
begin

IF @top > 0
    SET ROWCOUNT @top

    SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id=OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)] FROM 
    (
    SELECT  QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name], CONVERT(numeric(15, 2), (((CONVERT(numeric(15, 2), SUM(i.reserved)) * (SELECT low FROM TEST_CS.dbo.spt_values WHERE number=1 AND type='E')) / 1024.)/1024.)) AS [Total space used (MB)]
    FROM    sysindexes i INNER JOIN sysobjects o 
            ON i.id=o.id AND 
        ((@include_system_tables=1 AND o.type IN ('U', 'S')) OR o.type='U') AND 
        ((@include_system_tables=1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped')=0))
    WHERE   indid IN (0, 1, 255)
    GROUP BY    QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))

    ) as a

 ORDER BY [Total space used (MB)] DESC
 SET ROWCOUNT 0
end

Upvotes: 0

Views: 141

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269439

As the error implies, rows is a reserved word in Sybase. So, you want to surround it with square braces: [rows].

The list of Sybase reserved words is here.

Upvotes: 1

Related Questions