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

checking length and triming the value at ','!

Hi all!

I have a record coming from the database having of F_name,L_name, and M_name being concatenated and separated with comma. In an expression of a textbox control value I would like to check the length of this record and if it is more than 12 only to take the first values till the comma’,’ which appears prior to the 12^th character and truncate the rest. For example:

Record from the database= FirstName,LastName,MiddleName

1) GAUTHIER, JEFFREY, Jane Desired result GAUTHIER, b/c if Last name is added it will be more than 12.

2) Jane, JEFFREY, GAUTHIER Desired result Jane, b/c if Last name and first name are added it will be more than 12.

3) JEFFREY, Job, GAUTHIER Desired result JEFFREY, Job, b/c if First name is added it will be more than 12.

4) JEFFRE, Job, G Desired result JEFFREY, Job, G etc b/c all the values are not more than 12

So would you mind helping me on how the expression looks like?

Thank you in advance!

Try this expression:

=Iif(Fields!name.Value.ToString().Length() > 12, Iif(Fields!name.Value.ToString().Substring(0, 12).LastIndexOf(",") <> -1, Fields!name.Value.ToString().Substring(0, Fields!name.Value.ToString().Substring(0, 12).LastIndexOf(",")), Fields!name.Value.ToString().Substring(0, 12)), Fields!name.Value)

hehe, it's big and ugly but it does what you want. You could use embedded code to make it prettier.

|||

Hi Ryan,

That doesn't work if the three names' total length is less than 12. I just ran it on my name database and some come up as #error#.. Like "Hat, Jo, Ty" ...

pl

|||

Ugh...You're right. Ok, try this instead, add the following embedded code to your report:

Function TrimWithCommas(ByVal name As Object) As String

if (name is Nothing) then
TrimWithCommas = Nothing
end if

Dim nameString as String = name.ToString()
if (nameString.Length() > 12) then
Dim index as Integer = nameString.SubString(0, 12).LastIndexOf(",")
if (index <> -1) then
nameString = nameString.SubString(0, index)
else
nameString = nameString.SubString(0,12)
end if
end if
TrimWithCommas = nameString
End Function

Then use the following expression in the textbox you want the name to appear in:

=Code.TrimWithCommas(Fields!name.Value)

|||

Hi Ryan,

Excellent! That worked on 17000 employees! No errors!

Thanks for the lesson.

pl

|||

Hi,

I've been trying to write this up in the Expression editor, and, if the first 12 bytes of the name does not contain a comma, it gives an error. Here is the code:

=Iif( len(Fields!EMPLOYEE_NAME.Value) > 12,
Iif( instrrev( left( Fields!EMPLOYEE_NAME.Value.ToString, 12 ), ",",12) > 0,
left(Fields!EMPLOYEE_NAME.Value, instrrev( left( Fields!EMPLOYEE_NAME.Value.ToString, 12), ",",12 ) - 1 ) ,
left(Fields!EMPLOYEE_NAME.Value, 12)),
left(Fields!EMPLOYEE_NAME.Value,12))

It seems that the true and false clauses are evaluated every time, and the case where the "instrrev" fails (evaluates to zero), causes the whole condition to die...

I'm just starting to learn. Did I miss something?

Thanks.