Re: call Stored procedure with no params

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 14:36:19 -0800

I describe how to do this in my book. It's not documented in the ADO classic
docs (I know 'cause I wrote part of those docs many years ago).

All stored procedures (SQL Server) are exposed as methods of the Connection
object. This means you can execute a procedure "Fred" like this:

        myCn.Fred

It's that easy. Ok, so you have parameters (most SPs do). In this case code:

    myCn.Fred "my string parameter", 5522.22

Where 5522.22 is a non-string parameter value.

Ok, so your SP returns a rowset. Well, you need to create a live Recordset
to capture it. In this case code:

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset ' Do not code Dim rs as New
Recordset

Next, code the SP and add the "rs" variable to the end--it's always the last
argument after the parameters:

    myCn.Fred "my string parameter", 5522.22, rs

No, this technique is not supported in ADO.NET nor is it supported using COM
interop in VB.NET. It only works by accident in VB6.

hth 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"CSHENG" <anonymous@discussions.microsoft.com> wrote in message
news:ECBD777C-382D-4D5E-807F-005260428672@microsoft.com...
> CAN YOU TELL HOW TO EXECUTE IN ONE line code? How do i pass the
parameters?
>
> thanks

Relevant Pages

  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.sqlserver.server)
  • Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... having executing Sysbase stored procedures via JDBC. ... stored procedure which contains DDL and with AutoCommit set to false I ... The explanation for this behaviour I have found is that the JDBC ... I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Re: Sybase, JDBC, AutoCommit, DDL IN TRAN
    ... > having executing Sysbase stored procedures via JDBC. ... > stored procedure which contains DDL and with AutoCommit set to false I ... > The explanation for this behaviour I have found is that the JDBC ... > I need to be able to set AutoCommit to false and execute multiple ...
    (comp.lang.java.databases)
  • Re: Records lost in an ADOStoredProc
    ... Use a thread to fire off the stored procedures so that your application ... Let's suppose it updates ... > the CacheSize is set to 1; it is worse when I increase the CacheSize. ... > I don't need to show records, only to execute the store procedure. ...
    (borland.public.delphi.database.ado)
  • Re: Question about how to differentiate between user functions and
    ... - All stored procedures return a value even when you do not explicitly use ... RETURN to exit from your sp ... this line for all sps ... >> trying to add functionality to this application to be able to execute ...
    (microsoft.public.sqlserver.programming)