Syntax error converting the varchar value 'NULL' t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Syntax error converting the varchar value 'NULL' t

Author

Topic

jim65
Starting Member

35 Posts

Posted - 2006-03-10 : 10:37:46

when run this stored procedures I got the error message which is
"Syntax error converting the varchar value 'NULL' to a column of data type int."

Any Idea will be helpful

CREATE PROCEDURE dbo.asp_InsertBorrowerfromCDB (@CustNum varchar(30), @UserID varchar(7)) AS

INSERT INTO adBorrower
(BorrowerName, BorrowerMainAdress, BorrowerZip, ClosingDate, LenderEnumber, ProposedTCC, BankID, CustNum, RiskRatingID)
SELECT CustNam, CustAddr, CustZip, EstablishDt, @UserID, NetExposure, BankNum, CustNum, RiskRating
FROM CDB.dbo.tblCustomer
Where CustNum = @Custnum

--get my new record ID
Declare @NewRecord int
SELECT @NewRecord = @@Identity

-- Inserts the Borrower as the default Entity ID (Main Contact) into adBorrowerEntity
INSERT INTO adBorrowerEntity
(BorrowerID, Title, MainContact, TypeID, ContactAddress, ContactZipCode, ContactCustNum)
select @NewRecord, CustNam, '1', '1', CustAddr, CustZip, @CustNum
FROM CDB.dbo.tblCustomer
Where CustNum = @Custnum
GO

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-10 : 10:57:52

If this is the complete procedure, it's tough to tell exactly where your implicit conversion is happening. You may have to go through tblCustomer to find 'NULL's. You could either update your table to change 'NULL's to NULL or build a case statement for that field for the insert statement

Of course, I'm not sure why you'd store the string 'NULL' as a value.

BTW, you should use SCOPE_IDENTITY for retreiving the last ID. @@IDENTITY returns the last identity value regardless of scope.

jim65
Starting Member

35 Posts

Posted - 2006-03-10 : 12:05:14

Thanks a lot. I found the value RiskRating(varchr) FROM table tblCustomer may be NULL.
RiskRatingID(int) from adBorrower table.

after I changed the SP, I got another error message "Syntax error converting the varchar value 'N' to a column of data type int."

CREATE PROCEDURE dbo.asp_InsertBorrowerfromCDB (@CustNum varchar(30), @UserID varchar(7)) AS

Declare @newRiskID varchar
Declare @RiskID int

SELECT @newRiskID = RiskRating FROM CDB.dbo.tblCustomer
Where CustNum = @Custnum

if @newRiskID = NULL
set @RiskID = 0
else
set @RiskID = CAST(@newRiskID AS int)

INSERT INTO adBorrower
(BorrowerName, BorrowerMainAdress, BorrowerZip, ClosingDate, LenderEnumber, ProposedTCC, BankID, CustNum, RiskRatingID)
SELECT CustNam, CustAddr, CustZip, EstablishDt, @UserID, NetExposure, BankNum, CustNum, @RiskID
FROM CDB.dbo.tblCustomer
Where CustNum = @Custnum

GO

Kristen
Test

22859 Posts

Posted - 2006-03-11 : 11:51:13

"Declare @newRiskID varchar"

You need a size for the varchar definition

"set @RiskID = CAST(@newRiskID AS int)"

This is probably where the problem is arising. Is RiskRating in CDB.dbo.tblCustomer a VARCHAR rather than an INT?

If so you probably need some sort of test to prove that it is number before you attempt casting it to INT

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources