|
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. |
|
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.) |
|
If you are in iSQL you could use describe PROCEDURE MyCoolProcedure 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; 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
|
select * into dba.MyNewTable from dba.MyCoolProcedure() where 0 = 1 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
|
It seem that Sybase database is powerful enough and the forum is nice. I try your answers on next Monday.
How to deal with it in Sybase ASE? I ask it here because I can not access ASE newsgroups from my environment now.
This is not an ASE forum.