Re: Return value in stored procedure

"Man T" <alan_nospam_pltse@xxxxxxxxxxxx> wrote in message
news:4828264c@xxxxxxxxxxxxxxxxxxxxxxxxx
I want to return a value 0 or 1 from the stored procedure in SQl 2000.
How do I get this return value if I use TADOPStoredProcedure? Or do I need
to use TADOQuery?


CREATE PROCEDURE dbo.gt_IsAddressOnHold (@Address varchar(40), @City
varchar(40), @State varchar(15), @PostalCode varchar(5), @CtryID varchar(2))
AS

SET NOCOUNT ON /* Suppressed the " Rows affected" message */

DECLARE
@Result Integer

SET @Result = 0 -- Default value
-- Your exectuting code

Return @Result
-------------------------

In Delphi (you can use TADOStoredProc, TADOQuery, but I would use
TADOCommand)

Using the TADOCommand component, setting the appropriate properties (at
design time), in particular change the CommandType to cmdStoredProc (the
CommandText property will changed to a drop-down of stored procedures
(selecting the stored procedure of choice)

To make sure, select the Parameters property and verify Delphi 'filled in'
the Parameters collection with the stored procedure parameters, in
particular RETURN_VALUE

In Delphi code (datamodule/form method). Disclaimer, code is off the top of
my head, there are probably syntax errors:

function TdmData.IsAddressOnHold (const Address, City, State, PostalCode,
CtryID :String) : Boolean;
begin
with gt_IsAddressOnHold do
begin { Start of with ADO command do }
try { Start of try...except block }
//Set the corresponding properties
Execute();
Result := (Parameters.ParamByName('@RETURN_VALUE').Value = 1);
except
on e: Exception do
begin
Result := False;
//Raise a more meaningful/user-friendly error, log the
error, set a property, etc
end;
end; { End of try...except block }
end; { End of with ado command do }
end;



.

Relevant Pages

  • Re: Return value in stored procedure
    ... What if I use TAdoStoredProcedure? ... Using the TADOCommand component, ... select the Parameters property and verify Delphi 'filled in' ... begin {Start of with ADO command do} ...
    (borland.public.delphi.database.ado)
  • RE: [MSSQL2000] Stored procedures behaving weird?
    ... as recordset and not output param. ... > Now, since the stored procedure returns data trough OUTPUT parametar, I ... > doesn't return ANY data using OUTPUT parametars), I need to use ADO Command? ...
    (microsoft.public.sqlserver.programming)
  • RE: [MSSQL2000] Stored procedures behaving weird?
    ... as recordset and not output param. ... > Now, since the stored procedure returns data trough OUTPUT parametar, I ... > doesn't return ANY data using OUTPUT parametars), I need to use ADO Command? ...
    (microsoft.public.vb.database)
  • HELP !! Running SQL 2000 using ADO command object from Access 2000
    ... I'm running a SQL 2000 stored procedure from Access client using ... SQLOLEDB provider and ADO Command object. ...
    (microsoft.public.access.adp.sqlserver)
  • Gettng ADO recordset from nested stored procedures problem
    ... I have a stored procedure wich takes the document type as input parametar ... Now, since the stored procedure returns data trough OUTPUT parametar, I ... I understand that this particular example should be dealt with ADO Command ...
    (microsoft.public.data.ado)