• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Call a Stored Procedure inside of a SELECT statement

 
Peter Rooke
Ranch Hand
Posts: 851
1
Java Linux Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 851
1
Java Linux Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
by function I meant a user defined function wrapped in your own package (which would contain the cursor logic.
 
Peter Rooke
Ranch Hand
Posts: 851
1
Java Linux Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Arh - cheers. I think then I might as well just continue on using cursors, even if performance may be an issue.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic