getting returned value from stored procedure

From: Bill (wwvanselow_at_truecommerce.com)
Date: 10/08/03 

Date: Wed, 8 Oct 2003 10:29:14 -0500

I'm using Delphi 5, and Microsoft ActiveX Data Objects 2.1 Library Version:
2.1

I'm using Command to try to get the returned values of a stored procedure
from a SQLServer DB. How do I set the returned value as a recordset? Here
is my code that I've tried. Is there a easier way for me to do this?

(I'm having trouble, because 2.1 does not have "adExecuteRecord" as a const
for the execute option)

stored procedure...
create procedure sptc_get_unique_id
as

declare @id bigint
set @id = isnull((select top 1 NEXTID from TC_UNIQUE_ID),1)
if exists (select top 1 NEXTID from TC_UNIQUE_ID)
  update TC_UNIQUE_ID set NEXTID = @id + 1
else
  insert into TC_UNIQUE_ID (NEXTID) values (@id + 1)
return @id

Delphi code...
var
  Com: Command;
  Res: Recordset;
  Conn:Connection;
  records, Param : OleVariant;
  try
    try
      { create the ADO connection, based on application config settings }
      try
        OleCheck(CoCreateInstance(CLASS_Connection, nil, CLSCTX_ALL,
IID__Connection, Conn));
        Conn.Open(sConnection, sUserName, sPassword, 0);
      except
        on E:Exception do
          raise Exception.Create('An error occured while connecting to the
database. Please make sure the database is available and the connection
setting is set up properly.');
      end;
      { create an ADO result set and run the SQL query }
      OleCheck(CoCreateInstance(CLASS_Recordset, nil, CLSCTX_ALL,
IID__Recordset, Res));
      OleCheck(CoCreateInstance(CLASS_Command, nil, CLSCTX_ALL,
IID__Command, Com));

      Com.Set_ActiveConnection(Conn);

      Com.CommandType := adCmdStoredProc;
      Com.CommandText := 'SPTC_GET_UNIQUE_ID';
      Records :=Unassigned;
      Param := EmptyParam;
      Res := Com.Execute(records,Param,0)

      Result := Res;
    except
      on E:Exception do
      begin
        Res:=nil;
        raise;
      end;
    end;
  finally
    Conn:=nil;
    if Res <> nil then
      ADOCloseQuery(Res);
  end;

Relevant Pages

  • Re: How to handle concurrency issue with better performance?
    ... Would you put the timestamp check in the stored procedure on the server ... that have been modified since they were pulled from the database. ... select command). ... client) downgrade very much in using such ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Not Getting results from Stored procedure
    ... So, running a stored procedure from VB6 can be done fairly easily and while there are fewer whitepapers on how to best do so, I thought my website might still have a few but there are not that many left--the problem is, Microsoft is pulling old unsupported content from MSDN so a lot of those links are gone. ... In any event, when calling a stored procedure, you need to include phrase SET NOCOUNT ON, so that the 'records affected' message that occur from results of Stored proc processing aren't returned to the calling application. ... These messages appear to be returned to the calling application as a recordset. ... when calling a stored proc you need ignore the command object and use a syntactically correct string as the source parameter for the recordset's 'Open' method. ...
    (microsoft.public.vb.database)
  • Re: How to handle concurrency issue with better performance?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... that have been modified since they were pulled from the database. ... select command). ... I create stored procedure for the update command and assign ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Capturing SQL Stored Procedure Return Codes
    ... For a Recordset-returning Command: ... Returns a Recordset object reference or Nothing. ... My point is that I can force the SQL stored procedure to return a negative ... How do I capture return codes from a SQL Stored Procedure? ...
    (microsoft.public.access.modulesdaovba)
  • Re: Data Has Been Changed Issue
    ... Database & recordset variables should be desassigned before you exit ... > I am using command buttons to call code from modules. ... > Public Sub DeleteChargeOffRecords() ...
    (microsoft.public.access.modulesdaovba)
Loading