Delete and return data

Is there anyway for a stored procedure to delete a record but also return
the data from the record? My first guess would be to copy all the returned
fields to temp variables but this seems awkward. The call will be in a
transation so if I fail to receive the data the transaction will be rolled
back thus saving the data.
I'm thinking something like this but am looking for a better suggestion
(note very simplified example).
CREATE PROCEDURE [dbo].[procName]
(
@.val1 int,
@.val2 int
)
AS
DECLARE @.@.tV1 int
DECLARE @.@.tV2 int
DECLARE @.@.tV3 int
SELECT @.@.tV1=V1, @.@.tV2=V2, @.@.tV3=V3 FROM tb1 WHERE @.val1=V1 AND @.val2=V2
DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
SELECT @.@.tV1 as 'V1', @.@.tV2 as 'V2', @.@.tV3 as 'V3'
Regards,
JohnWhy not this:
SELECT ... FROM tb1 WHERE @.val1=V1 AND @.val2=V2
DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
"John J. Hughes II" <no@.invalid.com> wrote in message
news:O85n3SrkFHA.3336@.tk2msftngp13.phx.gbl...
> Is there anyway for a stored procedure to delete a record but also return
> the data from the record? My first guess would be to copy all the
> returned fields to temp variables but this seems awkward. The call will
> be in a transation so if I fail to receive the data the transaction will
> be rolled back thus saving the data.
> I'm thinking something like this but am looking for a better suggestion
> (note very simplified example).
> CREATE PROCEDURE [dbo].[procName]
> (
> @.val1 int,
> @.val2 int
> )
> AS
> DECLARE @.@.tV1 int
> DECLARE @.@.tV2 int
> DECLARE @.@.tV3 int
> SELECT @.@.tV1=V1, @.@.tV2=V2, @.@.tV3=V3 FROM tb1 WHERE @.val1=V1 AND @.val2=V2
> DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
> SELECT @.@.tV1 as 'V1', @.@.tV2 as 'V2', @.@.tV3 as 'V3'
> Regards,
> John
>|||You've almost got it right, but use update locks when reading out the row.
I prefer to return data for a single row in output parameters instead of
returning a result set.
"John J. Hughes II" <no@.invalid.com> wrote in message
news:O85n3SrkFHA.3336@.tk2msftngp13.phx.gbl...
> Is there anyway for a stored procedure to delete a record but also return
> the data from the record? My first guess would be to copy all the
returned
> fields to temp variables but this seems awkward. The call will be in a
> transation so if I fail to receive the data the transaction will be rolled
> back thus saving the data.
> I'm thinking something like this but am looking for a better suggestion
> (note very simplified example).
> CREATE PROCEDURE [dbo].[procName]
> (
> @.val1 int,
> @.val2 int
> )
> AS
> DECLARE @.@.tV1 int
> DECLARE @.@.tV2 int
> DECLARE @.@.tV3 int
> SELECT @.@.tV1=V1, @.@.tV2=V2, @.@.tV3=V3 FROM tb1 WHERE @.val1=V1 AND
@.val2=V2
> DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
> SELECT @.@.tV1 as 'V1', @.@.tV2 as 'V2', @.@.tV3 as 'V3'
> Regards,
> John
>|||Your method would only return that one row was deleted and not tell me what
was deleted. In my example I would lose the data for V3.
Regards,
John
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:OxDxCvrkFHA.2916@.TK2MSFTNGP14.phx.gbl...
> Why not this:
> SELECT ... FROM tb1 WHERE @.val1=V1 AND @.val2=V2
> DELETE tb1 WHERE @.val1=V1 AND @.val2=V2
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:O85n3SrkFHA.3336@.tk2msftngp13.phx.gbl...
>|||Thanks for the feedback... I can see the advantage of both. I need to do
this with several table, some of which have a lot of columns and was trying
to avoid the maintenance headache but I guess I have.
Regards,
John
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:O$wSSpskFHA.2156@.TK2MSFTNGP14.phx.gbl...
> You've almost got it right, but use update locks when reading out the row.
> I prefer to return data for a single row in output parameters instead of
> returning a result set.
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:O85n3SrkFHA.3336@.tk2msftngp13.phx.gbl...
> returned
> @.val2=V2
>|||That's why I left the example "open" - SELECT ... FROM tb1
You can replace the ... with all the columns that you need.
SELECT V1, V2, V3 FROM tb1 WHERE @.val1=V1 AND @.val2=V2
"John J. Hughes II" <no@.invalid.com> wrote in message
news:OWX7wXukFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Your method would only return that one row was deleted and not tell me
> what was deleted. In my example I would lose the data for V3.
> Regards,
> John
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:OxDxCvrkFHA.2916@.TK2MSFTNGP14.phx.gbl...
>|||Yes I understood that. The problem is the SQL is only going to return the
last result which is the delete command. I won't know what the query result
was from the select.
Regards,
John
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:u9t3YcukFHA.320@.TK2MSFTNGP09.phx.gbl...
> That's why I left the example "open" - SELECT ... FROM tb1
> You can replace the ... with all the columns that you need.
> SELECT V1, V2, V3 FROM tb1 WHERE @.val1=V1 AND @.val2=V2
> "John J. Hughes II" <no@.invalid.com> wrote in message
> news:OWX7wXukFHA.3256@.TK2MSFTNGP12.phx.gbl...
>|||I really didn't understand what you meant by this so I re-read the post
carefully and finally figured it out.
I had not seen that this procedure would be called from another procedure.
Brian give you the best solution.
If the delete only affects 1 row, return the values in Output parameters.
If not, I think that you're stuck with using a temp table.
See this site for more information:
http://www.sommarskog.se/share_data.html
"John J. Hughes II" <no@.invalid.com> wrote in message
news:%23hgaXT6kFHA.1968@.TK2MSFTNGP14.phx.gbl...
> Yes I understood that. The problem is the SQL is only going to return the
> last result which is the delete command. I won't know what the query
> result was from the select.
> Regards,
> John
> "Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
> news:u9t3YcukFHA.320@.TK2MSFTNGP09.phx.gbl...
>sql