I'm using ADS 11.10.0.20. I get the error "Cannot SELECT from a procedure with no output or varying output parameters", although the stored procedure has been setup with varying output.

Example SQL:

Create Procedure spARAccList ( CURSOR VARYING OUTPUT ) begin

select * from ARAcc;

end;

Select t.* from (Execute Procedure spARAccList()) t

asked 04 Oct '14, 09:57

Rafick's gravatar image

Rafick
2393311
accept rate: 33%


The Varying Output is the issue. If an SP has been setup with Varying Output you can not use the SP in a SELECT statement / FROM caluse. You can only execute it directly. (i.e. execute procedure....)

If you want to use an SP in the FROM clause (i.e. select field1 from (execute procedure...) as t) you need to specify the output parameters directly.

From the help:

Note A stored procedure with VARYING OUTPUT cannot be used directly in the FROM clause of a SELECT statement, and it cannot be used as the source of a cursor variable in SQL script.

[ALTER | CREATE] PROCEDURE

link

answered 06 Oct '14, 09:42

Edgar%20Sherman's gravatar image

Edgar Sherman
5.4k13185
accept rate: 25%

Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
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

Tags:

×40

Asked: 04 Oct '14, 09:57

Seen: 2,443 times

Last updated: 06 Oct '14, 09:42

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.