February 23, 2010 at 7:30 am
Hi,
1) I have created the following procedure and i would like to pass sid , mid , cid , iid row value to error table (error_log_temp) to check and rectify the error in that transaction. Culd you advise that.
2) can I count the number of rows having the error.
3) I have created a unique constraint on the table and if i exec the procedure ,I can see the error in query analyzer but not in the error_log_temp table.
Pls advise.
Procedure Code:
Create procedure st_temp_p
as
begin
declare
@package_name varchar(50),
@procedure_name varchar(50),
@step_name varchar(50),
@execution_date datetime,
@additional_Information varchar(50),
@rowscompleted int,
@rowsinerror int,
@error int,
@batchid int,
@RowCountSav int
begin transaction
INSERT INTO st_temp
( sid ,
mid ,
cid ,
iid ,
papernum ,
setid ,
deptid )
SELECT
S_REF,
M_REF,
C_REF,
m_id,
EL_ID,
VERSION,
S_CODE
FROM TT_Temp
WHERE NOT EXISTS
(
SELECT
sts.sid,
st.mid,
st.iid
FROM stumodules_temp stumodules
WHERE
AND st.sid = TT_Temp.S_REF
AND ----------------
)
SET @RowCountSav = @@ROWCOUNT
set @error=@@error
if @Error <> 0 --if error is raised
begin
goto LogError
end
commit transaction
insert into tvu_master_audit_temp(
package_name,
procedure_name,
step_name,
execution_date,
additional_Information ,
rowscompleted,
rowsinerror)
Values('Update Package','st','INSERT',getDate(),'Completed',@RowCountSav,0)
goto ProcEnd
LogError:
rollback transaction
declare @ErrMsg varchar(1000)
select @ErrMsg = [description] from master.dbo.sysmessages
where error = @Error
insert into error_log_temp (batchid,package_name,step_name,LogDate,Source,ErrMsg)
values (@batchid,'Update Package','INSERT',getdate(),'st_temp_p',@ErrMsg)
ProcEnd:
end
February 23, 2010 at 9:01 am
Have you looked at mofiying your T-SEL to use Try--Catch
Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.
From Books On Line:
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH
February 24, 2010 at 7:20 am
As Ron has pointed out, you should be using the Try-Catch error handling. But you also have an error in your code. The @@error function returns the error for the last statement executed this is from BOL:
Returns the error number for the last Transact-SQL statement executed.
When Microsoft® SQL Server™ completes the execution of a Transact-SQL statement, @@ERROR is set to 0 if the statement executed successfully. If an error occurs, an error message is returned. @@ERROR returns the number of the error message until another Transact-SQL statement is executed. You can view the text associated with an @@ERROR error number in the sysmessages system table.
Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.
and in your case the last statement executed is:
SET @RowCountSav = @@ROWCOUNT
Thus @error is ALWAYS 0 because that line always executes successfully. Your issue is that you can't reverse the order of your variable assignment because if you do that @@ROWCOUNT will always be 1.
Using Try - Catch would look like this:
DECLARE
@error INT,
@rows INT ;
CREATE TABLE #test (id TINYINT) ;
CREATE TABLE #error (num INT, msg NVARCHAR(2048));
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO
#test (id)
VALUES
(1000 -- id - tinyint
) ;
SET @rows = @@ROWCOUNT ;
/* Insert Into log table goes here
in your code you have the commit BEFORE
the logging occurs. I'm not sure you want that so
I moved it before the commit */
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @errmsg NVARCHAR(2048);
SET @error = ERROR_NUMBER() ;
SET @errmsg = ERROR_MESSAGE();
ROLLBACK TRANSACTION
/*insert into error log*/
INSERT INTO #error
( num, msg )
VALUES ( @error, -- num - int
@errmsg -- msg - nvarchar(2048)
)
END CATCH
SELECT * FROM #error AS E
SELECT
@error,
@errmsg,
@rows,
@@trancount ;
DROP TABLE #test ;
DROP TABLE #error
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply