Create Dynamic Query in Sybase SELECT TOP X

Question

I am wondering how to do the following in Sybase 15.

DECLARE @DEPTH INT
SET @DEPTH = 8    
SELECT TOP @DEPTH  * FROM Table

It gives me the following error : Error (102) Incorrect syntax near '@DEPTH'.

I tried using TOP (@DEPTH), same way as sql server but it recognizes it as an error.

Error (14216) Function 'TOP' not found. If this is a SQLJ function or SQL function, use sp_help to check whether the object exists (sp_help may produce a large amount of output).

Thank you

score 2 · Accepted Answer · answered Aug 25 '17 at 05:40

2

The answer is:

DECLARE @DEPTH INT
SET @DEPTH = 8    
SET ROWCOUNT @DEPTH
SELECT * FROM Table
SET ROWCOUNT 0

answered Aug 25 '17 at 05:40

Adam Leszczyński

1,079
7
13

score 0 · Answer 2 · answered Aug 26 '17 at 14:02

0

TOP accepts only an integer constant, not a variable. In case you are running the very latest (16.0 SP03) you can use ROWS LIMIT @v instead of TOP.

As mentioned by others, you can work around the TOP @v issue by constructing a query dynamically.

answered Aug 26 '17 at 14:02

RobV

2,263
1
11
7

score -1 · Answer 3 · answered Aug 25 '17 at 12:13

There are a couple options:

wrap the select in a pair of set rowcount commands (see Adam's answer for an example)
build a dynamic query and run it via execute() (see below for an example)

dynamic query example:

declare @depth int, @query varchar(16384)
select  @depth = 8
select  @query = 'select top '+convert(varchar,@depth)+' * from table'
execute(@query)
go

Create Dynamic Query in Sybase SELECT TOP X

3 Answers3