Win a copy of Secure Financial Transactions with Ansible, Terraform, and OpenSCAP this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Rob Spoor
  • Henry Wong
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh
  • Jj Roberts

Call a Stored Procedure inside of a SELECT statement

 
Bartender
Posts: 1087
19
Mac OS X IntelliJ IDE Oracle Spring VI Editor Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Bartender
Posts: 1087
19
Mac OS X IntelliJ IDE Oracle Spring VI Editor Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Bartender
Posts: 1087
19
Mac OS X IntelliJ IDE Oracle Spring VI Editor Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ew. You guys are ugly with a capital UG. Here, maybe this tiny ad can help:
SKIP - a book about connecting industrious people with elderly land owners
https://coderanch.com/t/skip-book
reply
    Bookmark Topic Watch Topic
  • New Topic