1

What is the equivalent of SQL Server's varchar(max) data type in ASE?

By ASE I mean what used to be known as Sybase ASE, which is now owned by SAP.

I'm attempting to execute a piece of dynamically constructed T-SQL that is longer than 8,000 chars.

If I declare a variable with varchar(max), ASE chokes with:

Incorrect syntax near the keyword 'max'.

My next thought was to try the text datatype. But alas:

You specified an incorrect datatype for the variable containing the 'execute immediate' command string.

A minimal, complete, and verifiable example:

DECLARE @cmd varchar(max) --change this to varchar(4000) and it works
SET @cmd = 'SELECT 1;'
EXEC (@cmd);
GO

Changing the variable declaration to text or whatever will suit your purposes. I need to execute a dynamic command that is probably 50,000 characters long.

Alternately, I could use a cursor, but I am unfamiliar with how cursors work on ASE.

Note that in SQL Anywhere, you can define an equivalent to the varchar(max) datatype as declare @cmd long varchar;, however that won't work in ASE.

0
1

ASE's (var)char max is 16384. (assuming ASE 15.x/16.x)

If the query can be broken into chunks, with each chunk no larger than 16K, then you could try concatenating them inside the execute() call.

From the documentation for the execute() command:

There are no restrictions to the number of characters supplied with the literal string.

And from Example 6 (same link) we see a simple example:

execute ('select ' + @columname + ' from ' + @tablename + ' where id=3')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.