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

define number of rows returned from query

Is there a way to limit the number of rows returned from a reporting services query?

as in my query will return 500 rows but i only want the 1st 5 rows.

right after the select you would use "TOP 5". So it would look something like

select top 5 * from table_name

Default value for lookup

How can I specify a default value to return into the lookup field for Lookup Transformation - if the value did not found on the Reference Table?

Do I have to this another way? (after doing the lookup)

/Yossi D.Perhaps you could go to the Advanced tab and adjust the lookup query SQL to return a default value. (I haven't tried it.)

Or

Set the error option to ignore, so your rows that failed to return a value just flow down the main output with null values. You can then use a Derived Column to test for NULL and assign the default value.