how to select a record and delete from table afterwards?

[wataru]

is it possible to retrieve a record and delete it from the table afterwards,
in a single sql statement?

right now, i'm doing it in 2 separate sql statements:
1. select * from table where idNum = 1 (store it in a record set variable)
2. delete from table where idNum = 1

i was wondering if i could execute it in a single sql statement.

thanks,
joe

[Shuja Ali]

If you are using SQL 2005 then you should look at "OUTPUT DELETED" keyword on MSDN. This can be used with the delete statement to output the deleted rows into a table, etc.

[wataru]

hi,

sorry, i'm just using sql server 2000. are there any other ways?

how about with stored procedure? is it advisable to use stored procedure
with this problem?

thanks.
joe

[Alsvha]

In 2000 there's no way that I know of - then you must select and delete in two different operations.

[wataru]

oh i see.

by the way, does it make a difference for the processing speed if i'll call select and delete in 2 separate sql statements or is it better to use single stored procedure( for select & delete) for this one?

[Alsvha]

Well - with 1 stored procedure you'll only call the database once, and depending on how your data-access code layer is set up it would mean one less round trip to the database.

So personally - I'd wrap it in a Stored Procedure.

[wataru]

hi,

thanks for the response.

i'm still a newbie in stored procedure, is it ok if you can give me an example on how to create
that kind of stored procedure?

and also how to call it from visual c++ application and returns recordset?

thank you very much,
joe