January 4, 2005 at 8:13 am
This section of a SP should generate an error(Fields not found) at the Insert Statement, but my ReturnCode and ReturnMsg both return null. What am I missing? Disclaimer:Very little Sql Server experience.
BEGIN TRAN
INSERT INTO tblUtilityBills(Installation_Key, Reading_Month, Reading_Year, Util_Usage,
Cost_Total, UM_Key, Reading_Type, Facility_Key)
SELECT Installation_Key, Reading_Month, Reading_Year, Util_Usage, Cost_Total,UM_Key,
Reading_Type,Facility_Key
FROM #tmpUtilityBills Tmp
WHERE NOT EXISTS(
SELECT 1
FROM tblUtilityBills UB
WHERE UB.Installation_Key = tmp.Installation_Key
AND UB.Reading_Month =tmp.Reading_Month
AND UB.Reading_Year = tmp.Reading_Year
AND UB.UM_Key = tmp.UM_Key
AND UB.Facility_Key = tmp.Facility_Key)
IF @@Rowcount = 0
SELECT @ReturnCode = @@RowCount, @Returnmsg = ' Error Updating tblUtilityBills'
IF @@ERROR = 0
Begin
COMMIT TRAN
SELECT @ReturnCode =1, @ReturnMsg = cast(@DetailCount as nvarchar(3)) + ' Successfully Uploaded.'
End
ELSE
Begin
ROLLBACK TRAN
SELECT @ReturnCode =@@RowCount, @ReturnMsg = char(13) + cast(@DetailCount as nvarchar(3)) + ' Upload Failed.'
End
RETURN
GO
fryere
January 4, 2005 at 8:54 am
You have several issues in that code:
ex: you have to perform the read of all the interested variables in one shot like: select @rcnt = @@rowcount. @err = @@error and then proceed with your logic using the local variables.
2. You said the error expected was FIELD not found ?
- to make sure you are executing everything by the standards always qualify your fields when more than one table is involved in the query ex: tmp.UM_key
- Why are you coding expecting a missing Field, there must be a better way
- NOT ALL errors are trappable with @@error. Although most of them are, you should know that some errors specially syntax or Trigger rollbacks simply abort the batch not permiting the execution any further, so your @@error reading statement won't even be executed
HTH
* Noel
January 4, 2005 at 5:09 pm
Apart from the other badness previously mentiond, here's the fundamental wrongness:
in the following code:
IF @@Rowcount = 0
SELECT @ReturnCode = @@RowCount, @Returnmsg = ' Error Updating tblUtilityBills'
IF @@ERROR = 0
Begin
COMMIT TRAN
SELECT @ReturnCode =1, @ReturnMsg = cast(@DetailCount as nvarchar(3)) + ' Successfully Uploaded.'
End
There may have been a value in @@error prior to the "IF @@rowcount", but the "IF @@rowcount.." statemtent will change the value in @@error and @@rowcount.
to do proper error checking, store the values of @@error and @@rowcount immediately following the statement you want to validate and then look at those:
UPDATE blah
SET ...
SELECT @err = @@ERROR, @rows = @@ROWCOUNT
IF @err <> 0 or @rows = 0 BEGIN
...something went wrong...
END
January 5, 2005 at 7:28 am
Noel/John,
Thanks for the advice I will implement your suggestions. That may explain the odd behavior with the @@rowcount value that I received as well.
BTW Noel, could you be more specific about 'qualify your fields?'
fryere
January 5, 2005 at 11:09 am
BTW Noel, could you be more specific about 'qualify your fields?'
When I mentioned Qualify the fields I meant to put the ALIAS or Tablename before it like
Select a.Fld1, a.Fld2... from tableName a
instead of
Select Fld1, Fld2 ... from tableName a
Rememeber this is consider a good practice, specially when more than one table are involved in the Source (FROM ) part of the statement
HTH
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply