How to create a table using the the output result of a stored procedure?

My purpose is getting the output data type of a stored procedure.

asked 30 Jun '11, 16:28

qkc1828's gravatar image

qkc1828
110349
accept rate: 0%

edited 04 Jul '11, 11:33

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665

2

It seem that Sybase database is powerful enough and the forum is nice. I try your answers on next Monday.

(02 Jul '11, 09:50) qkc1828

How to deal with it in Sybase ASE? I ask it here because I can not access ASE newsgroups from my environment now.

(04 Jul '11, 12:41) qkc1828
Replies hidden

This is not an ASE forum.

(04 Jul '11, 13:55) Dmitri

If I do understand you correctly, you want to get the data types of the result set of a stored procedure (instead of the result set itself). You can use the sa_describe_query() system procedure when using v10 or newer, e.g.

select * from sa_describe_query('select * from sa_conn_info()')

Or you can query the system catalog directly, such as

select parm_name, *
from sysprocparm spp key join sysprocedure sp
where parm_type = 1 and proc_name = 'sa_conn_info'
order by 1

(For older versions, the query might have to be adapted.)

permanent link

answered 01 Jul '11, 07:00

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 01 Jul '11, 07:01

If you are in iSQL you could use

describe PROCEDURE MyCoolProcedure
permanent link

answered 01 Jul '11, 05:12

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k243561
accept rate: 17%

edited 01 Jul '11, 05:13

Why this does not work?

describe PROCEDURE dbo.getMsgList;


Could not execute statement.

incrrect syntax near the keyword "PROCEDURE".

Sybase error code=156,SQLstate='ZZZZZ'.

(04 Jul '11, 09:14) qkc1828
Replies hidden

What version are you using?

AFAIK, the Interactive SQL DESCRIBE statement is available since v10 and does only work with DBISQL, not with dbisqlc.

(04 Jul '11, 09:25) Volker Barth

@qkc1828: I turned your "answers" into comments to the according answers - IMHO this makes it far easier to follow a discussion thread. You can add comments (instead of answes) by using the "add new comment" button right beneath any question/answer.

(04 Jul '11, 09:34) Volker Barth

select @@version

Adaptive Server Enterprise/15.0.2/EBF14332/P/NT(I*86)/Windows2000....

(04 Jul '11, 09:44) qkc1828

Glad that I've asked for the version:)

Please note, this is a forum on Sybase SQL Anywhere, not Sybase ASE, which is a different database product.

I would suggest to have a look at on one of Sybase's ASE newsgroups - see this link.

(04 Jul '11, 09:54) Volker Barth

The above answers work nicely for procedures that have a single result set where the server is able to determine the schema for the result set when the procedure is created. This could be because the procedure describes the columns using a RESULT clause or because the server is able to determine the result set by inspecting the procedure. In the case of multiple result sets or procedures where the schema changes from call to call, you could use instead the sa_describe_cursor system procedure. This system procedure describes the current result set of an open cursor, which could be a cursor over a CALL statement. You can even use this if the procedure returns multiple result sets; just use RESUME until the cursor is positioned on the proper result set. However, unlike the other answers, this approach requires actually executing the procedure.

The sa_copy_to_temp_table system procedure can also be used if you want to copy the contents of the cursor to a temporary table (although the column names are generated as col1..colN instead of coming from the statement).

create temporary procedure P_DynamicResult( @type int )
begin
    if @type = 1 then
        select * from sys.systab;
    else
        select * from dbo.rowgenerator;
    end if;
end;

begin
    declare crsr cursor for call P_DynamicResult(0);
    open crsr;
    select * from sa_describe_cursor('crsr');
    close crsr;
end;
permanent link

answered 04 Jul '11, 07:55

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

An in-depth addition to the topic, of course:)

It should be noted that both system procedures require v12 and above, AFAIK.

(04 Jul '11, 08:56) Volker Barth
Replies hidden

It should also be noted that it requires SQL Anywhere :)

(04 Jul '11, 11:20) Breck Carter

maybe time for a feature request:

ALTER DATABASE SET RDMS = 'SQLANYWHERE'

:)

(04 Jul '11, 11:29) Justin Willey

As default, right?

(04 Jul '11, 11:32) Volker Barth

Too funny... a question about ASE gets a "Popular Question" badge, and answer about SQL Anywhere gets selected as the correct answer :) http://4.bp.blogspot.com/_qQmMZeCw7l4/TPjb1clQzcI/AAAAAAAAAAQ/5O468-VilB8/s1600/Three%2BStooges%2BMovie.jpg

(04 Jul '11, 13:22) Breck Carter

Who's ASE in the image? The one standing in the middle?

(05 Jul '11, 08:20) Volker Barth

No, that's me... the other two are you and Ivan :)

(05 Jul '11, 08:42) Breck Carter

I must be getting old - can't remember the scene and the cause...

(05 Jul '11, 08:45) Volker Barth
showing 1 of 8 show all flat view
select *
  into dba.MyNewTable
  from dba.MyCoolProcedure()
 where 0 = 1
permanent link

answered 01 Jul '11, 04:09

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%

This does not work either?

select *

into dba.MyNewTable

from dbo.getMsgList()

where 0 = 1


Could no execute statement.

incrrect syntax near ")".

Sybase error code=102,SQLstate='4200'

(04 Jul '11, 09:18) qkc1828
Replies hidden

What database software are you using? I don't think you are using any version of SQL Anywhere, which is what this forum is all about.

(04 Jul '11, 09:45) Breck Carter

select @@version

Adaptive Server Enterprise/15.0.2/EBF14332/P/NT(I*86)/Windows2000....

(04 Jul '11, 09:51) qkc1828

This is the wrong forum to ask about ASE, sorry.

(04 Jul '11, 11:20) Breck Carter

Yes, already noted in the comments on Thomas's answer...

(04 Jul '11, 11:25) Volker Barth

Besides that, I'm glad the question was raised here - the SQL Anywhere-related answers are very helpful.

(04 Jul '11, 11:27) Volker Barth
showing 1 of 6 show all flat view
Your answer
toggle preview

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

Question tags:

×106
×78

question asked: 30 Jun '11, 16:28

question was seen: 2,939 times

last updated: 05 Jul '11, 08:45