Is there a Set Based Method in SQL Anywhere similar to SQL Server?

I'm attempting to load the result set from the sa_disk_free_space system stored procedure so that I can manipulate it and present it in MB. Below is what I have so far.

BEGIN 
    DECLARE LOCAL TEMPORARY TABLE varMoveToSQL 
        (mydbspace_name nvarchar(20), myfree_space int, mytotal_space int);

    WITH Dataset (dbspace_name,free_space,total_space) AS (
                EXEC  sa_disk_free_space
                )

    INSERT INTO varMoveToSQL SELECT * FROM Dataset;

    SELECT * FROM varMoveToSQL;
END

sa-17

asked 01 Mar '19, 14:37

dangris
69●2●2●6
accept rate: 0%

edited 06 Mar '19, 03:12

Mark Culp
24.9k●10●141●297

flat view

One Answer:

active answers oldest answers newest answers popular answers

It is way easier with SQL Anywhere. You can simply use a stored procedure in the FROM clause of a SELECT or INSERT, no need to store the procedure's result set in a temporary table.

Just something like "select * from my_proc(params) sp where..."

permanent link

answered 01 Mar '19, 15:09

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 01 Mar '19, 15:09

...for example:

SELECT *
  FROM sa_disk_free_space();
dbspace_name,free_space,total_space
'system',331786121216,979443380224
'translog',331786121216,979443380224
'temporary',331786121216,979443380224

If you really want a separate local temporary table...

SELECT *
  INTO #varMoveToSQL
  FROM sa_disk_free_space();
SELECT * 
  FROM #varMoveToSQL;
dbspace_name,free_space,total_space
'system',331776749568,979443380224
'translog',331776749568,979443380224
'temporary',331776749568,979443380224

(02 Mar '19, 09:03) Breck Carter

Thanks for the quick response. That's exactly what I needed.

(04 Mar '19, 09:26) dangris

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-17 ×246

question asked: 01 Mar '19, 14:37

question was seen: 865 times

last updated: 06 Mar '19, 03:12

SAP SQL Anywhere

Is there a Set Based Method in SQL Anywhere similar to SQL Server?

Follow this question

Related questions