How to return an IDENTITY value of the record a Stored Procedure has just UPDATED???

How to return an IDENTITY value of the record a Stored Procedure has just UPDATED???

Post by Alan Maile » Tue, 08 May 2001 02:55:37



How would you go about returning the value of the Identity field of a
record your Stored Procedure has just UPDATED?

Thanks in advance.

 
 
 

How to return an IDENTITY value of the record a Stored Procedure has just UPDATED???

Post by Mary Chipma » Tue, 08 May 2001 04:03:22


You could return it in an output parameter (more efficient) or a
recordset by inserting a SELECT statement after the UPDATE.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

On Sun, 06 May 2001 17:55:37 GMT, Alan Mailer


>How would you go about returning the value of the Identity field of a
>record your Stored Procedure has just UPDATED?

>Thanks in advance.


 
 
 

How to return an IDENTITY value of the record a Stored Procedure has just UPDATED???

Post by Tibor Karasz » Tue, 08 May 2001 04:00:13


Alan,

You mean UPDATE and not INSERT (for INSERT, use the SCOPE_IDENTITY
function)?

If you mean UPDATE, just do a SELECT based on the same WHERE clause as the
update was based on:


Then you can return this variable as a result set or an out parameter.
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.


Quote:> How would you go about returning the value of the Identity field of a
> record your Stored Procedure has just UPDATED?

> Thanks in advance.

 
 
 

How to return an IDENTITY value of the record a Stored Procedure has just UPDATED???

Post by Ange » Tue, 08 May 2001 04:51:08


why scope ?



> Alan,

> You mean UPDATE and not INSERT (for INSERT, use the SCOPE_IDENTITY
> function)?

> If you mean UPDATE, just do a SELECT based on the same WHERE clause as the
> update was based on:


> Then you can return this variable as a result set or an out parameter.
> --
> Tibor Karaszi, SQL Server MVP
> FAQ from Neil at: http://www.sqlserverfaq.com
> Please reply to the newsgroup only, not by email.



> > How would you go about returning the value of the Identity field of a
> > record your Stored Procedure has just UPDATED?

> > Thanks in advance.

 
 
 

How to return an IDENTITY value of the record a Stored Procedure has just UPDATED???

Post by Tibor Karasz » Tue, 08 May 2001 05:33:09




return the value (possibly null) generated from the INSERT in the trigger.
SCOPE_IDENTITY does not have that problem.

I'm afraid that I don't understand above. Can you elaborate?
--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.


> why scope ?


 
 
 

How to return an IDENTITY value of the record a Stored Procedure has just UPDATED???

Post by BP Margoli » Tue, 08 May 2001 08:11:40


Angel,

when an UPDATE occurs. Alan was very clear in his original post that he is
doing UPDATEs, not INSERTs.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> why scope ?

> "Tibor Karaszi"



> > Alan,

> > You mean UPDATE and not INSERT (for INSERT, use the SCOPE_IDENTITY
> > function)?

> > If you mean UPDATE, just do a SELECT based on the same WHERE clause as
the
> > update was based on:


> > Then you can return this variable as a result set or an out parameter.
> > --
> > Tibor Karaszi, SQL Server MVP
> > FAQ from Neil at: http://www.sqlserverfaq.com
> > Please reply to the newsgroup only, not by email.



> > > How would you go about returning the value of the Identity field of a
> > > record your Stored Procedure has just UPDATED?

> > > Thanks in advance.

 
 
 

1. Using @@Identity as a return value of a stored procedure

I am using a stored procedure to insert a new record into a table
where the key field is an identity field.
A trigger on the table is updating the audit table with the inserted
record.  The audit table also has an identity field. I am using the
following code to return the identity of the original record added
(not the audit table record).





        else

I am using an ADO connection within a VB application to recieve the
return value, using lrecIdentity(0).Value to examine the return value.

99.9% of the time this all works but occasionally I get a return value
in the region of 16787108-16787192 which does not correspond to any
identity value in either the target table or audit table.

Can anyone suggest why this is happening?

Dave

2. Deleting problem in VFP5.0 - Primary key constraint

3. INSERT stored procedure with identity column return value

4. How to print HTML ..

5. return select values in stored procedure to a calling stored procedure

6. Binary Sort conversion

7. ADO don't get output param or return value when stored procedure contains update

8. Possible bug with RefreshLink method in Access/VB with SQL 60 tables

9. RETURN VALUE in stored procedure not always returning to ADO

10. Stored procedure returning empty output parameters or return value

11. How to capture the return value and output parameter value of a stored procedure in VB

12. Stored procedure returning variable value, not column value?