checking the error code

Question.
I am sending a list of comma separated codes as a parameter to stored
procedure. As I parse the list, I put them in the table variable (or temp
table) using insert statement.
For example if the parameter is 1001,3233,4444,2223,4452,5523, after the
parsing, the table variable will contain 6 rows with the respective code.
Now, do I need to check @.@.ERROR after each insert statement whether it was
successful or not even though I am dealing with table variable?
Of course I am trying to avoid following approach
WHILE EndOfListCondition
BEGIN
BEGIN
INSERT tblCode(Code) VALUES(@.ParsedCodeID)
SET @.ErrorCheck = @.@.ERROR
IF @.ErrorCheck <> 0 GOTO ERR_HANDLER
END
END
and use
WHILE EndOfListCondition
BEGIN
BEGIN
INSERT @.tableVariable(Code) VALUES(@.ParsedCodeID)
-- ? Do I need error check here?
END
END
INSERT tblCode(Code)
SELECT Code
FROM @.tableVariable
Thanks
JJustin (jyang@.ioutsource.info) writes:
> I am sending a list of comma separated codes as a parameter to stored
> procedure. As I parse the list, I put them in the table variable (or temp
> table) using insert statement.
> For example if the parameter is 1001,3233,4444,2223,4452,5523, after the
> parsing, the table variable will contain 6 rows with the respective code.
> Now, do I need to check @.@.ERROR after each insert statement whether it was
> successful or not even though I am dealing with table variable?
Now that is a good question. Fortunately, the answer in SQL 2005 is
simple: set up a TRY-CATCH handler that embraces the entire procedure
as a matter of routines.
In SQL 2000, it's worse. My own approach is that I always check
@.@.error after every INSERT, DELETE and UPDATE although that I know
that some of them just can go wrong. Then again, I'm not checking
error after SELECT, despite a lot of our client code is running with
SET LOCK_TIMEOUT 29000. (Which is a stpuid idea.)
What I do to make this easier, is that I have some clips in my editor
(Textpad), so that I can easily add error checking. I'm also formatting
it compactly, so that it does not take up too much space.
Error handling in SQL Server 2000 is a boring business. For more
advice, my article http://www.sommarskog.se/error-handling-II.html
could be of interest.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx