July 24, 2012 at 11:43 am
Does anyone have a simple Stored Procedure that uses a MERGE Statement uses BEGIN TRY/CATCH Error handling and Store the records Counts using the OUTPUT Clause?
I'm looking for a simple example?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 24, 2012 at 12:44 pm
This thread has a good example of using OUTPUT with MERGE.
http://www.sqlservercentral.com/Forums/Topic1325508-391-1.aspx
There is nothing magical about try/catch blocks in t-sql. They work just like the same constructs in every programming language.
http://msdn.microsoft.com/en-us/library/ms175976.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2012 at 1:29 pm
Sean Lange (7/24/2012)
This thread has a good example of using OUTPUT with MERGE.http://www.sqlservercentral.com/Forums/Topic1325508-391-1.aspx
There is nothing magical about try/catch blocks in t-sql. They work just like the same constructs in every programming language.
Thanks Sean.
I have used try/catch in some C# Code and VB.NET that I wrote years ago. I guess I was not clear how to position them in a MERGE.
Thanks again.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 24, 2012 at 1:33 pm
Welsh Corgi (7/24/2012)
Sean Lange (7/24/2012)
This thread has a good example of using OUTPUT with MERGE.http://www.sqlservercentral.com/Forums/Topic1325508-391-1.aspx
There is nothing magical about try/catch blocks in t-sql. They work just like the same constructs in every programming language.
Thanks Sean.
I have used try/catch in some C# Code and VB.NET that I wrote years ago. I guess I was not clear how to position them in a MERGE.
Thanks again.
Well much like any try/catch concept you want it to wrap the ENTIRE process. You can't put a try/catch around a portion of a sql statement and MERGE is a single statement. Either the entire MERGE is successful or not.
begin try
--Entire MERGE statement here
end try
begin catch
--whatever code you want to run when it fails
end catch
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2012 at 1:43 pm
Sean Lange (7/24/2012)
This thread has a good example of using OUTPUT with MERGE.http://www.sqlservercentral.com/Forums/Topic1325508-391-1.aspx
There is nothing magical about try/catch blocks in t-sql. They work just like the same constructs in every programming language.
Keep in mind that OUTPUT allows you to capture the detail of what was changed. You cannot directly use aggregation using an OUTPUT clause. As in - this is NOT legal
update blah
set mycol='SomeNewValue'
output count(inserted.ID) -- Aggregation is not allowed in OUTPUT
If all you are looking for is a count of affected records, use select @@rowcount
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 24, 2012 at 1:55 pm
Matt Miller (#4) (7/24/2012)
Sean Lange (7/24/2012)
This thread has a good example of using OUTPUT with MERGE.http://www.sqlservercentral.com/Forums/Topic1325508-391-1.aspx
There is nothing magical about try/catch blocks in t-sql. They work just like the same constructs in every programming language.
Keep in mind that OUTPUT allows you to capture the detail of what was changed. You cannot directly use aggregation using an OUTPUT clause. As in - this is NOT legal
update blah
set mycol='SomeNewValue'
output count(inserted.ID) -- Aggregation is not allowed in OUTPUT
If all you are looking for is a count of affected records, use select @@rowcount
True and good reminder. You could however do something like this:
update blah
set mycol='SomeNewValue'
output @@rowcount
The only thing to remember here is that you would get an output row for every row that was updated. You should be able to devise any number of ways to get around this.
You could create a temp table to hold your output and then build your insert using a group by or distinct to your audit table. You could forgo the OUTPUT and just use @@rowcount in your next statement. There are a number of ways you can accomplish this. It all comes down to what you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2012 at 2:25 pm
Sean Lange (7/24/2012)
True and good reminder. You could however do something like this:
update blah
set mycol='SomeNewValue'
output @@rowcount
(snipped for brevity)
Neat idea, but unfortunately it doesn't seem to work. @@rowcount gets set AFTER the operation completes, so during the query isn't accurate.
If you want to see what I mean - try this:
begin tran
delete from tally
output @@rowcount
where n%3=0
select @@rowcount
rollback
assuming your tally table is 1M rows like mine, you'd get 333K of 0's, and one correct answer.
But yes - your point is correct that you could use output to tget to your count (eventually). It's just a lot more work if you're simply looking for affected rows (as opposed to a summary of what got updated, what got inserted and what got deleted, for example).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 24, 2012 at 2:51 pm
Matt Miller (#4) (7/24/2012)
Sean Lange (7/24/2012)
True and good reminder. You could however do something like this:
update blah
set mycol='SomeNewValue'
output @@rowcount
(snipped for brevity)
Neat idea, but unfortunately it doesn't seem to work. @@rowcount gets set AFTER the operation completes, so during the query isn't accurate.
If you want to see what I mean - try this:
begin tran
delete from tally
output @@rowcount
where n%3=0
select @@rowcount
rollback
assuming your tally table is 1M rows like mine, you'd get 333K of 0's, and one correct answer.
But yes - your point is correct that you could use output to tget to your count (eventually). It's just a lot more work if you're simply looking for affected rows (as opposed to a summary of what got updated, what got inserted and what got deleted, for example).
Doh! I thought it would work. I would not recommend that as the best approach of course but it could work. To directly deal with the OP's rowcount I would agree that dropping the output portion and doing that as a separate query would be the best approach.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2012 at 3:02 pm
Sean Lange (7/24/2012)
Matt Miller (#4) (7/24/2012)
Sean Lange (7/24/2012)
This thread has a good example of using OUTPUT with MERGE.http://www.sqlservercentral.com/Forums/Topic1325508-391-1.aspx
There is nothing magical about try/catch blocks in t-sql. They work just like the same constructs in every programming language.
Keep in mind that OUTPUT allows you to capture the detail of what was changed. You cannot directly use aggregation using an OUTPUT clause. As in - this is NOT legal
update blah
set mycol='SomeNewValue'
output count(inserted.ID) -- Aggregation is not allowed in OUTPUT
If all you are looking for is a count of affected records, use select @@rowcount
True and good reminder. You could however do something like this:
update blah
set mycol='SomeNewValue'
output @@rowcount
The only thing to remember here is that you would get an output row for every row that was updated. You should be able to devise any number of ways to get around this.
You could create a temp table to hold your output and then build your insert using a group by or distinct to your audit table. You could forgo the OUTPUT and just use @@rowcount in your next statement. There are a number of ways you can accomplish this. It all comes down to what you are trying to do.
I have used @@ROWCOUNT and @@ERROR in the past primarily for error hadling (pre 2005).
I need to log the number of record in the Source and Destination System before and after the MERGE or Whatever methos that I use. I also need to track the Number of Updates as well. I need to do this in Staging and in the Data Warehouse Load.
Thank you.
I also need to embed error handling within the MERGE. I just was sure about the Syntax.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 11:23 am
Sean Lange (7/24/2012)
There is nothing magical about try/catch blocks in t-sql. They work just like the same constructs in every programming language.
Sean,
I got this code from your link. How would I modify the SP to insert a Table?
This will end up in a SSIS Package. Currently I have an Error Handler but it only captured the Package, Task Error Number and Description. I guess that I could revise it but I believe that it would be better to capture at the Stored Procedure Level or both?.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 11:32 am
Modify your error proc (usp_GetErrorInfo) so that it inserts into a table, then you're good to go.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 25, 2012 at 12:50 pm
GSquared (7/25/2012)
Modify your error proc (usp_GetErrorInfo) so that it inserts into a table, then you're good to go.
I did that and it worked fine.
I have a very basic question.
If I execute a stored procedure that Inserts records into a table but executes the Error Procedure that now inserts record into an ErrorLog Table.
When I execute the Stored Procedure it returns executed successfully.
Should I not use a return code as well?
I will be placing this in an SSIS Package and I'm not sure I want to handle a failure.
Thanks for the help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 12:54 pm
Welsh Corgi (7/25/2012)
GSquared (7/25/2012)
Modify your error proc (usp_GetErrorInfo) so that it inserts into a table, then you're good to go.I did that and it worked fine.
I have a very basic question.
If I execute a stored procedure that Inserts records into a table but executes the Error Procedure that now inserts record into an ErrorLog Table.
When I execute the Stored Procedure it returns executed successfully.
Should I not use a return code as well?
I will be placing this in an SSIS Package and I'm not sure I want to handle a failure.
Thanks for the help.
The most common thing here is to use RAISERROR. You have your auditing logic in place, now just raise the same error that caused it fail in the first place. You have already captured the error details.
http://msdn.microsoft.com/en-us/library/ms178592.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2012 at 1:24 pm
Sean Lange (7/25/2012)
Welsh Corgi (7/25/2012)
GSquared (7/25/2012)
Modify your error proc (usp_GetErrorInfo) so that it inserts into a table, then you're good to go.I did that and it worked fine.
I have a very basic question.
If I execute a stored procedure that Inserts records into a table but executes the Error Procedure that now inserts record into an ErrorLog Table.
When I execute the Stored Procedure it returns executed successfully.
Should I not use a return code as well?
I will be placing this in an SSIS Package and I'm not sure I want to handle a failure.
Thanks for the help.
The most common thing here is to use RAISERROR. You have your auditing logic in place, now just raise the same error that caused it fail in the first place. You have already captured the error details.
Thank you!
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 25, 2012 at 2:31 pm
Sean Lange (7/25/2012)
Welsh Corgi (7/25/2012)
GSquared (7/25/2012)
Modify your error proc (usp_GetErrorInfo) so that it inserts into a table, then you're good to go.I did that and it worked fine.
I have a very basic question.
If I execute a stored procedure that Inserts records into a table but executes the Error Procedure that now inserts record into an ErrorLog Table.
When I execute the Stored Procedure it returns executed successfully.
Should I not use a return code as well?
I will be placing this in an SSIS Package and I'm not sure I want to handle a failure.
Thanks for the help.
The most common thing here is to use RAISERROR. You have your auditing logic in place, now just raise the same error that caused it fail in the first place. You have already captured the error details.
Sean,
I'm clear how I tie in the RAISEERROR with the error in the CATCH Block?
BEGIN TRY
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return
-- error information about the original error that
-- caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply