Error converting data type varchar to numeric.

DECLARE @.ENTITY nvarchar (100)

set @.ENTITY = 'AccidentDimension'

DECLARE @.FIELD nvarchar (100)

set @.FIELD = 'JurisdictionState'

DECLARE @.KEYID nvarchar (100)

SET @.KEYID = '1234567890'

DECLARE @.VALUE nvarchar (100)

SET @.VALUE = 'WI'

DECLARE @.WC_TABLE NVARCHAR(100)

SET @.WC_TABLE = 'WorkingCopyAdd' + @.ENTITY

DECLARE @.SQL1 NVARCHAR (1000)

SET @.SQL1 = 'INSERT INTO ' + @.WC_TABLE+ ' (Claim, '+ @.Field +') VALUES ('''+ @.KEYID +''', '''+@.VALUE+''')'

EXECUTE sp_executesql @.SQL1

Can somebody help me. I get this error:

Error converting data type varchar to numeric.

while executing this Dynamic TSQl Command

Odd; what is the data type of the "JurisdictionState" column. The other column should be fine, but also what is the data type of the "Claim" column.

|||

The statement you are executing is

INSERT INTO WorkingCopyAddAccidentDimension (Claim, JurisdictionState) VALUES ('1234567890', 'WI')

There is a bigger question than the varchar/numeric conversion, and that is why you are using dynamic SQL, but to answer the question you asked, it doesn't look like there should be a problem. Is this the exact code that is failing, or might it be failing on another @.KEYID value?

I suggest you look at www.sommarskog.se/dynamic_sql.html for more on dynamic SQL, even thought it's tangential to your specific question here.

Steve Kass

Drew University

http://www.stevekass.com

|||

Claim is of type: varchar

JurisdictionState is char of length 2

I seperated and inserted with each colunm at a time to know if its problem with Claim or JursidictionState. Well, both of the time I received same error.

|||The error may be caused by an incorrect default, as in teh following sample:

CREATE TABLE a(i int, j INT DEFAULT('acs'))
GO
INSERT a(i)VALUES(1)

also the error might be caused by a trigger. Do you have triggers on the table?|||

Well I had DEFAULT ' ' (A Blank / Space)

Is this the problem ?

|||

yes.. yes..

Always try to stick with the column datatype while giving the default value..

I am really wondering how SQL Server allows to create the default value with different datatype..

|||

CREATE TABLE WCAddAcciDim

([Claim] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ' ',

[JurisdictionState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ' '

)

GO

INSERT dbo.WCAddAcciDim(Claim , JurisdictionState)VALUES('12', 'IN')

INSERT dbo.WCAddAcciDim(Claim , JurisdictionState)VALUES('1234567890', 'WI')

select * from WCAddAcciDim

DROP TABLE WCAddAcciDim

Well I see no problem when I execute this.

And when I execute this below line, just this line, without use of Dynamic SQL

INSERT dbo.WorkingCopyAddAccidentDimension(Claim, JurisdictionState) VALUES ('1234567890', 'WI')

I get error.

Very Strange.

I created teh table WorkingCopyAddAccidentDimension same way as I did above, infact I copied those lines and rename the tabel name thats it.

What might be the Hidden error, Any idea please

|||

What is the error message you are getting… Verify the table schema using..

Sp_help WorkingCopyAddAccidentDimensiona

|||

Mani:

When I removed the Defaults I was able to update and Insert as wanted and required. However i have NULLS in the rest of colunm. This table has 68 Colunms and I have multiple tables around 6of similar number of colunms. Now I am using the data from these Staging/ WorkingCopy tables and Inserting it back to main Table. Where certain colunm cannot be null. If there is a null in certain colunm it will not allow me to insert it. Thast why I chose to make BLANK as a default in WorkingCopy Table.

Now that I have remove BLANK/ SPACE from teh default, is there any Standard way of Replacing these NULLS with the Blank /Space. Could you please suggest any way to do this?

What does MS SQL Standards have to say on this?

What I could think of is to replace each colunm with a space were ever there is NULL but I guess this is not the standard way of doing. Any suggestion or any modification on re-creating WorkingCopy Table with Defaults?

|||

You are in wrong direction, The default won’t help you here..

The default only activated when you have no entry on the INSERT statement. When you try to INSERT the NULL value the Default value will not be taken, rather it will store as NULL.

In single word, the DEFAULT value only stored when there is no value/no entry specified in the insert query…

As per the BOL,

Column definition

No entry, no DEFAULT definition

No entry, DEFAULT definition

Enter a null value

Allows null values

NULL

Default value

NULL

Disallows null values

Error

Default value

Error

So, you have to use the ISNULL function to fix your problem.

Code Snippet

Create table #Staging1

(

Id int,

Name varchar(10)

)

Insert Into #Staging1 Values(1, NULL);

Insert Into #Staging1 Values(1, 'test');

Go

Create table #Main

(

ID int NOT NULL,

Name varchar(10) NOT NULL DEFAULT ('')

);

--Will Work Fine

Insert Into #Main(ID)

Select ID From #Staging1

--Should Fail

Insert Into #Main(ID,Name)

Select ID,Name From #Staging1

--Will Work

Insert Into #Main(ID,Name)

Select ID,Isnull(Name,'') From #Staging1

|||

Excellent

Got it

Thanks a lot Mani