Board index » delphi » Unable to read value of output parameter from stored procedure

Unable to read value of output parameter from stored procedure

I need to add a record to a table in a SQL Server database and get the
table's resulting primary key.  I am able to do this with an ADODataset
component, but I would much rather do it using an ADOCommand component since
reading an output parameter from a stored procedure is about 25X faster than
reading from a returned recordset.

This line from a stored procedure works and returns the new primary key in a
recordset:
  Insert Into [TableName] (Field1, Field2) Values (@param1, @param2) SELECT
@@IDENTITY AS 'PrimaryKeyField'

If I try to run this line from an ADOCommand component, the new record is
added but the return parameter is never set:
  Insert Into [TableName] (Field1, Field2) Values (@param1, @param2) Select
@outParam = @@IDENTITY
Ditto for this line:
  Insert Into [TableName] (Field1, Field2) Values (@param1, @param2) Set
@outParam = @@IDENTITY
This line seems like it should work, but won't compile:
  Insert Into [TableName] (Field1, Field2) Values (@param1, @param2) Select
@outParam = @@IDENTITY AS 'PrimaryKeyField'

From what I've read it may be that the wrong cursor is being used, but that
is not a property available to an ADOCommand component.  Might there be
something else that I am not doing correctly?

 

Re:Unable to read value of output parameter from stored procedure


Hi Brad,

Quote
> From what I've read it may be that the wrong cursor is being used, but
that
> is not a property available to an ADOCommand component.  Might there be
> something else that I am not doing correctly?

I think that your stored proc is working fine.  From the help files:

<<TADOCommand is most often used for executing data definition language
(DDL) SQL commands or to execute a stored procedure that does not return a
result set. For those SQL statements that do return a result set, a
TADODataSet, TADOQuery, or TADOStoredProc is better suited to the task. The
Execute method is, however, capable of returning a recordset. A separate ADO
dataset component must be supplied to use that recordset, though.>>

Not a clue how to make that last line a reality, but in any case, why not
try TADOStoredProc?  Maybe its execution speed is comparable.

Cheers,
Nelson

Re:Unable to read value of output parameter from stored procedure


The time taken by an ADOCommand and an ADOStoredProcedure are nearly
identical (over 20X faster than an ADODataset).  I wouldn't have thunk it
since the ADOStoredProc is a descendant of the ADODataSet and still requires
a resulting recordset.  Thanks!

Quote
"NelsonH" <nel...@backupmanager.com> wrote in message

news:3eee41af$1@newsgroups.borland.com...
Quote
> Not a clue how to make that last line a reality, but in any case, why not
> try TADOStoredProc?  Maybe its execution speed is comparable.

Other Threads