aspose file tools*
The moose likes JDBC and the fly likes Call a Stored Procedure inside of a SELECT statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Call a Stored Procedure inside of a SELECT statement" Watch "Call a Stored Procedure inside of a SELECT statement" New topic
Author

Call a Stored Procedure inside of a SELECT statement

Peter Rooke
Ranch Hand

Joined: Oct 21, 2004
Posts: 805

Just wondering if it is possible to perform a stored procedure call within a select statement within T-SQL [in Sybase]. So maybe something like this:

SELECT column1,
column2,
foobar = exec foobar(@foo, @bar) as 'foobar'
FROM TABLE T (etc...)

Mostly, I tend just to use loop using a cursor - but cursors are not that good for performance on Sybase databases...


Regards Pete
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
You may be confusing a function with a procedure. If you have an operation with a FROM clause that requires a rowset be retrieved from a table/tables, then you create a function that you call from your select statement.

What is it you are trying to accomplish?
Peter Rooke
Ranch Hand

Joined: Oct 21, 2004
Posts: 805

Ok, firstly I know I can do this with a cursor, just loop through the contents of a select cusror and then call the required stored procedure from within the loop, passing in the values from the fetch statement.

Problem is cursors are not the quickest in Sybase (it's different in Informix and Oracle). So therefore I was wanting to try to perform this within a select statement.

Yes, quite happy with calling various functions abs(..), datediff(..) within the select resultset - I was trying to find if anyone had ever used a stored procedure instead of a function. The problem I think will be the way in which the procedure passes parameters back. One option I can think of is to wrap the procedure call with a function call...
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
by function I meant a user defined function wrapped in your own package (which would contain the cursor logic.
Peter Rooke
Ranch Hand

Joined: Oct 21, 2004
Posts: 805

Arh - cheers. I think then I might as well just continue on using cursors, even if performance may be an issue.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Call a Stored Procedure inside of a SELECT statement