|
|
Posted by Hugo Kornelis on 10/01/16 11:28
On Tue, 04 Oct 2005 07:16:55 GMT, Driesen via SQLMonster.com wrote:
>Hi Hugo
>
>Thanks for the reply. I will try IF ISNUMERIC(@sellername) = 1. I have also
>posted the complete procedure as well.
(snip)
Hi Driesen,
Thanks for posting this. I've been playing around with it, but I'm
afraid I can't give you a straight answer - only some wild speculations.
1. Do you use a case-sensitive collation? If so, check if you have both
a "sellers" and a "Sellers" table, and/or both a "Sellername" and a
"sellername" column in your table.
2. Did you verify that the value 'danwet w125 ' is indeed in the Sellers
table, in the row indicated by the value you passed for @ApplicationId?
And is it in the "sellername" column?
3. Have you already tried using the debugger to single-step through the
code and check the exact location where the error occurs? The line
number in an error message is sometimes a bit off. Though I have to
admit that in the lines surrounding line 231, I don't see any statement
that might cause this error either.
4. You might also do some "manual" debugging: add some PRINT statements
to show the value assigned to a character just after it's been set, and
such.
5. I don't think it's relateed, but the test
IF @sellername is not null OR @sellername <> ''
mystifies me. A @sellername equal to '' is not null, and will pass this
test. I think you meant to wrote
IF @sellername is not null AND @sellername <> ''
And because a NULL @sellername will return Unknown for the second test,
and an IF will only choose the THEN part if the result is True, this can
further be simplified to
IF @sellername <> ''
But apart from the error that prompted you to post here, I see some
other problems with this code. Nothing that will cause errors - but
you're wasting resources and hurting scalability of your application.
I stopped counting how often you use
SELECT ... FROM LoanDetail WHERE ApplicationId = @ApplicationId
A major performance booster would be to declare a bunch of variables at
the beginning of the proc, use ONE query to assign them with the correct
values, then sue the variables in the rest of the code. Like this:
SELECT @sellername = sellername,
@BuilderName = BuilderName,
@LoanAmount = LoanAmount,
.....
FROM LoanDetail
WHERE ApplicationId = @ApplicationId
....
--no special characters and numerics in sellers name - Driesen
--1412
IF @sellername <> ''
BEGIN
IF ISNUMERIC (@sellername)
INSERT #ERRORS (RuleNumber, ApplicationID, ClientId, SuccessInd)
VALUES (1412, @ApplicationId, 0, 'Y')
ELSE
INSERT #ERRORS (RuleNumber, ApplicationID, ClientId, SuccessInd)
VALUES (1412, @ApplicationId, 0, 'N')
END
...
But that's only the beginning. I have a hunch that this procedure is
called from a loop. And this loop processes a cursor to get all values
for @ApplicationId, one by one, and call this proc for each of them. Am
I correct? If the answer is yes, then please rewrite the procedure to
process all Applications at once. Get rid of the parameter and rewrite
the queries to something like this:
.....
--no special characters and numerics in sellers name - Driesen
--1412
INSERT #ERRORS (RuleNumber, ApplicationID, ClientId, SuccessInd)
SELECT 1412, a.ApplicationId, 0,
CASE WHEN ISNUMERIC (sellername) THEN 'Y' ELSE 'N' END
FROM LoanDetail AS l
JOIN TableThatHoldsApplicationIds AS a
ON a.ApplicationId = l.ApplicationId
WHERE l.sellername <> ''
It might even be possible (with some extra CASE expression) to combine
some (or even all) tests into one single INSERT statement, but I'll
leave that for you to explore.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Navigation:
[Reply to this message]
|