SEO

On Fri, 30 Sep 2005 06:17:33 GMT, Driesen via SQLMonster.com wrote:

>Hi guys
>
>I having trouble with this sproc. I get the following error when testing:
>
>Server: Msg 245, Level 16, State 1, Procedure UTL_CompletenessCheckLoan, Line
>231
>Syntax error converting the varchar value 'danwet w125 ' to a column of data
>type int.
>
>I have declared @Sellername as Varchar. Please can someone tell me what I'm
>doing wrong? All it needs to do is return the 'Y' value if there are numerics
>in the sellername.
>
>declare @sellername varchar(40),
> @applicationid INT
>
> select @sellername = sellername from Seller where ApplicationId =
>@ApplicationId
> If @sellername is not null or @sellername <> ''
> begin
>
>if (select isnumeric(Sellername) from seller where ApplicationId =
>@ApplicationId) = 1
> select 'Y'
>ELSE
> select 'N'
> end
>
>
>Thanks for any help
>Driesen

Hi Driesen,

In this code snippet, I see only one snippet that MIGHT cause this
error: if the column Seller.ApplicationId is not numeric, and stores the
value 'danwet w125 ' in one of the rows, you'd get this error when
executing the (first) SELECT statement. But I also note that the error
is reported at line 231, and you only posted about 10 lines. Maybe you
should repost the complete procedure?

Another thing: why don't you replace the select in the IF statement with
the variable you just retrieved in the previous statement?
IF ISNUMERIC(@sellername) = 1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hi Hugo

Thanks for the reply. I will try IF ISNUMERIC(@sellername) = 1. I have also
posted the complete procedure as well.


CREATE PROCEDURE dbo.UTL_CompletenessCheckLoan (@ApplicationId INT)
AS

DECLARE @ClientID INT,
@sellername varchar(40),
@BuilderName varchar(40)

SET NOCOUNT ON

CREATE TABLE #ERRORS(
RuleNumber INT,
ApplicationID INT,
ClientId INT,
SuccessInd CHAR(1))



--1
-- Loan Amount Not Captured
IF (SELECT LoanAmount FROM LoanDetail WHERE ApplicationId = @ApplicationId)
IS NULL OR
(SELECT LoanAmount FROM LoanDetail WHERE ApplicationId = @ApplicationId) =
0
INSERT INTO #ERRORS VALUES (1301,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1301,@ApplicationId,0,'Y')

--2
/*
IF (SELECT LoanAmount FROM LoanDetail WHERE ApplicationId = @ApplicationId) >
(SELECT PurchasePrice FROM LoanDetail WHERE ApplicationId = @ApplicationId)

INSERT INTO #ERRORS VALUES (1302,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1302,@ApplicationId,0,'Y')
*/
--3
-- Registered Amount must be the same or higher than the Loan Amount
IF (SELECT LoanAmount FROM LoanDetail WHERE ApplicationId = @ApplicationId)
<=
(SELECT RegistrationAmount FROM LoanDetail WHERE ApplicationId =
@ApplicationId)
INSERT INTO #ERRORS VALUES (1303,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1303,@ApplicationId,0,'N')

--4 Purchase date before today and after 2000

IF (SELECT PurchaseDate FROM LoanDetail WHERE ApplicationId = @ApplicationId)
BETWEEN
(SELECT '20000101') AND (select dateadd(day,-1,GETDATE()))
INSERT INTO #ERRORS VALUES (1304,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1304,@ApplicationId,0,'N')

--5
-- Deposit Details not Captured
IF (SELECT DepositInd FROM LoanDetail WHERE ApplicationId = @ApplicationId) =
'Y'
IF (SELECT (isnull(CashAmount,0) + isnull(SecurityAmount,0) + isnull
(SecurityBondsAmount,0) + isnull(SuretyshipAmount,0) + isnull(OtherAmount,0))
FROM LoanDetail WHERE ApplicationId = @ApplicationId) = 0
INSERT INTO #ERRORS VALUES (1305,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1305,@ApplicationId,0,'Y')

--6
-- Cost Details not captured
IF (SELECT AdditionalCostInd FROM LoanDetail WHERE ApplicationId =
@ApplicationId) = 'Y'
BEGIN
IF (SELECT (isnull(FurnishingsAmount,0) + isnull(TransferCosts,0) + isnull
(DepositCost,0) + isnull(OtherCost,0)) FROM LoanDetail WHERE ApplicationId =
@ApplicationId) > 0
INSERT INTO #ERRORS VALUES (1306,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1306,@ApplicationId,0,'N')
END

--7
-- The Repayment Day of the month must be between 1 and 31
if (SELECT RepaymentDayOfMonth FROM LoanDetail WHERE ApplicationId =
@ApplicationId) is not null
Begin
IF (SELECT RepaymentDayOfMonth FROM LoanDetail WHERE ApplicationId =
@ApplicationId) BETWEEN 1 AND 31
INSERT INTO #ERRORS VALUES (1307,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1307,@ApplicationId,0,'N')
End

--8
-- No Builder Details Captured
IF (SELECT LoanPurposeCode FROM LoanDetail WHERE ApplicationId =
@ApplicationId) = 2
BEGIN
IF (SELECT COUNT(*) FROM Builder WHERE ApplicationId = @ApplicationId) = 0
OR (SELECT CompletionDate FROM Builder WHERE ApplicationId = @ApplicationId)
is NULL
OR (SELECT BuilderPhoneCode FROM Builder WHERE ApplicationId =
@ApplicationId) is NULL
OR (SELECT BuilderPhone FROM Builder WHERE ApplicationId = @ApplicationId)
is NULL
OR (SELECT BuilderName FROM Builder WHERE ApplicationId = @ApplicationId)
is NULL
OR (SELECT BuildingContractAmount FROM Builder WHERE ApplicationId =
@ApplicationId) is NULL
INSERT INTO #ERRORS VALUES (1308,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1308,@ApplicationId,0,'Y')
END

--9
/*
IF (SELECT MarketValue FROM Property WHERE ApplicationId = @ApplicationId) <
(SELECT PurchasePrice FROM LoanDetail WHERE ApplicationId = @ApplicationId)

INSERT INTO #ERRORS VALUES (1309,@ApplicationId,0,'N')
ELSE
INSERT INTO #ERRORS VALUES (1309,@ApplicationId,0,'Y')
*/

--10
-- Client has an existing Homeloan but no Home Loan captured
SET @ClientId = 0

IF (SELECT ExistingHomeLoanInd FROM LoanDetail WHERE ApplicationId =
@ApplicationId) = 'Y'
BEGIN
SELECT @ClientId = ClientId FROM ApplicationClient WHERE ApplicationID =
@ApplicationId and ApplicationRoleCode = 1

IF (SELECT COUNT(*) FROM BankAccount BA JOIN ClientBankAccount CBA ON BA.
BankAccountId = CBA.BankAccountId
WHERE ClientId = @ClientId and AccountTypeId = 7) >= 1
INSERT INTO #ERRORS VALUES (1310,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1310,@ApplicationId,0,'N')
END

--11
-- If it is a Building Loan the Contract Amount must be captured
/*If (SELECT LoanPurposeCode FROM LoanDetail WHERE ApplicationID =
@ApplicationId) = 2
BEGIN
IF (SELECT BuildingContractAmount FROM Builder WHERE ApplicationID =
@ApplicationId) > 0
INSERT INTO #ERRORS VALUES (1311,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1311,@ApplicationId,0,'N')
END
*/
--12
-- If Property Type is Sectional Title then the Loan must not be a Building
Loan
/*
If (SELECT PropertyTypeCode FROM Property WHERE ApplicationID =
@ApplicationId) = 3
BEGIN
IF (SELECT LoanPurposeCode FROM LoanDetail WHERE ApplicationID =
@ApplicationId) <> 2
INSERT INTO #ERRORS VALUES (1312,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1312,@ApplicationId,0,'N')
END*/


--14
-- Complex Name not captured for a Sectional Title development
IF (SELECT PropertyTypeCode FROM Property WHERE ApplicationID =
@ApplicationId) = 3
BEGIN
IF (SELECT ComplexName FROM SectionalTitle WHERE ApplicationID =
@ApplicationId) IS NOT NULL
INSERT INTO #ERRORS VALUES (1314,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1314,@ApplicationId,0,'N')
END

--15
-- Surety Name and Surety Amount must be captured
IF (SELECT SuretyType FROM LoanDetail WHERE ApplicationID = @ApplicationId)
in (1,2)
BEGIN
IF ((SELECT SuretyshipDetails FROM LoanDetail WHERE ApplicationID =
@ApplicationId) IS NOT NULL
and (SELECT SuretyshipAmount FROM LoanDetail WHERE ApplicationID =
@ApplicationId) IS NOT NULL)
INSERT INTO #ERRORS VALUES (1315,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1315,@ApplicationId,0,'N')
END


--16
-- If repayment method is Debit Order then All Bank Details must be captured
IF (SELECT PaymentMethodCode FROM LoanDetail WHERE ApplicationID =
@ApplicationId) = 1
BEGIN
IF (SELECT PaymentBankAccountId FROM LoanDetail WHERE ApplicationID =
@ApplicationId) IS NOT NULL
INSERT INTO #ERRORS VALUES (1316,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1316,@ApplicationId,0,'N')
END

--17
--Check payment account is Current or Savings
if (select distinct PaymentBankAccountID from LoanDetail where ApplicationId
= @ApplicationId) is not null
Begin
if (select AccountTypeId from BankAccount BA join LoanDetail LD on BA.
BankAccountId = LD.PaymentBankAccountID where ApplicationId = @ApplicationId)
in (1,2)
INSERT INTO #ERRORS VALUES (1317, @ApplicationID, 0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1317, @ApplicationID, 0,'N')
End

--18
--if payment method is Debit Order then Payment freq must be captured
if (select PaymentMethodCode from LoanDetail where ApplicationId =
@ApplicationId) = 1
Begin
if (select isnumeric(InstalmentFrequency) from LoanDetail where ApplicationId
= @ApplicationId) = 1
INSERT INTO #ERRORS VALUES (1318,@ApplicationId,0,'Y')
ELSE
INSERT INTO #ERRORS VALUES (1318,@ApplicationId,0,'N')
End

--19
--Sellers IdNo and DOB must Match
if (select len(SellerIdNo) from Seller where Applicationid = @ApplicationId)
> 0
Begin
if (select substring(convert(char(8),SellerDateOfBirth,112),3 ,6) from seller
where Applicationid = @ApplicationId) =
(select substring(convert(char(15),SellerIdNo),1,6) from seller where
Applicationid = @ApplicationId )
INSERT INTO #ERRORS VALUES (1319,@ApplicationId,0,'Y')

ELSE
INSERT INTO #ERRORS VALUES (1319,@ApplicationId,0,'N')
End


--no special characters and numerics in builders/ contracters name - Driesen
--1411

select @BuilderName = buildername from builder where ApplicationId =
@ApplicationId

If @BuilderName is not null or @BuilderName <> ''
begin

if (select isnumeric(buildername) from builder where ApplicationId =
@ApplicationId) = 1
INSERT INTO #ERRORS VALUES (1411,@ApplicationId,0,'Y')

ELSE
INSERT INTO #ERRORS VALUES (1411,@ApplicationId,0,'N')

end


--no special characters and numerics in sellers name - Driesen
--1412
select @sellername = sellername from Seller where ApplicationId =
@ApplicationId
--select applicationid = 1000054939
If @sellername is not null or @sellername <> ''
begin

if (select isnumeric(Sellername) from seller where ApplicationId =
@ApplicationId) = 1
INSERT INTO #ERRORS VALUES (1412,@ApplicationId,0,'Y')

ELSE
INSERT INTO #ERRORS VALUES (1412,@ApplicationId,0,'N')

end


SELECT E.RuleNumber, E.ApplicationId, E.ClientId, CC.RuleDescription, CC.
ScreenName, Er.ErrorDescription, CC.BankID
FROM #Errors E
JOIN CompletenessCheck CC on E.RuleNumber = CC.RuleNumber
JOIN Error Er on CC.ErrorNumber = Er.ErrorNumber
WHERE SuccessInd = 'N'

RETURN 0

DROP TABLE #Errors
GO


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200510/1

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)

Hi Hugo

Thanks very much for the tips. I thought I was being safe by using all those
SELECT statements. I had no idea that my resources were being chunked. I am
going to look at re-writing all my procedures using your recommendations. As
for my original posting, I re-wrote the entire procedure and it seems to work
now. It looks exactly the same as the other one.

Thanks a million for all the help, Hugo.
Driesen


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200510/1