Re: Question about how to differentiate between user functions and stored procedures when using OLE DB to connect to SQL Server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 08/12/04 

Date: Thu, 12 Aug 2004 14:17:14 -0400

This should tell you quite a bit:

SELECT SPECIFIC_NAME, ROUTINE_TYPE
    FROM INFORMATION_SCHEMA.ROUTINES 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"Mac Dyer" <mac@simx.com> wrote in message
news:#CsxggJgEHA.1972@TK2MSFTNGP09.phx.gbl...
> Hi I am not 100% sure if this is the correct group to post in but.....
>     I was just wondering if there was an easy way( if one at all ) to be
> able to tell the differnce between stored procs and user functions through
> OLE DB connecting to SQL Server 2000.  I am building an application that
> connects to various databases, excel, access, sqlsvr, etc.via OLE DB.  I
am
> trying to add functionality to this application to be able to execute
stored
> procedures and User functions( for SQL SVR at least ).  The way I get the
> list of Procedures is through the DBSCHEMA_PROCEDURES rowset from OLE DB.
I
> am able to execute stored procedure just fine using the ODBC syntax (
which
> as far as I could tell was the most generic and accepted by all of the
> databases tested so far ) EX. { ? = CALL myproc( ?, ?,....) }.  My problem
> is with functions.  From what I have seen some of them I need to use a
> select statement to execute them: SELECT * FROM fn_func( ?, ?,.... ).
Also
> some functions require :: placed before the name to execute properly while
> others do not. ( This is from SQL SVRs Query Analyzer by right clicking on
> the function and choosing to script the object in a new window as |
EXECUTE.
> I do not feel that I can rely on the fact that the name starts with
> fn_...EX. fn_dblog.  Unfortunately ALL of the procedures ( which is both
> stored procs and user functions ) return in the Procedures rowset as
> DB_PT_FUNCTION which specifies each item is a function and has a return
> value ( there are 2 other options, 1 specifying a Procedure( ...no return
> value ) and the other that it does not know whether or not it has a return
> value ).  Is there some OTHER way to differentiate the 2( procedures and
> functions )?  Any help or suggestions would be greatly appreciated.  Thank
> you for your time.
> -Mac
>
>

Relevant Pages