Conversion failed when converting the varchar valu

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 2005 Forums

Transact-SQL (2005)

Conversion failed when converting the varchar valu

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-06 : 06:48:20

I am recieving the following error when executing the following code. Field MonData is nvarchar (80), the only integer feild in the concatination is @Cnt all other fields are varchar or nvarchar. If I remove the everything past space(22) it works fine. I double checked the length and it does not exceed 80 bytes.
Can anyone see what I am doing wrong?

INSERT INTO tbl_Export
Select DISTINCT TOP(1) space(32) + '9' + space(2) + @Cnt + space(2) + Sys + space(2) +
Prin + space(22) + 'TEN' + space(1) + 'ZZ' As MonData
FROM dbo.Trans
WHERE Sys = @vwSys AND Prin = @vwPrin

ERROR:
Conversion failed when converting the varchar value 'TEN' to data type int.

Any input is welcome, thanks so much.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-06 : 06:54:00

it's implicitly trying to cast it all as an int because that's what your first values are. convert your @cnt to a varchar for the concatenation

Em

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-06 : 10:02:06

Thank you so much elancaster for responding so quickly.

I ran it with just @cnt, sys, prin cast and I got a Null value in the output field. No fields showed up.

I changed my code to CAST everything as varchar or nvarchar. Since the output field (MonData) was nvarchar. The problem is I still got a null value in my output field.

Then I changed the output field (MonData) to varchar and all Cast all fields as varchar and I still got a Null value in the output field.

Well, at lest the error is gone (smile).

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-06 : 10:04:53

if any of your values are NULL then concatenating it will result in NULL. you can use coalesce to get round it though (you can find it in Books OnLine)

Em

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-06 : 11:39:21

When you check Help the example shown puts the COALESCE func inside the Cast func. But the example uses a Cast function for all field values. Can I use one CAST func for all of my field values even with the Space func in between them? I guess the bottom line is I'm just not sure how to structure it.

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-06 : 12:30:42

I also saw the CASE statement in HELP so I tried to use that and got the same results. I'm running out of options.

INSERT INTO tblExport -- HDR 020 Export HDR
Select DISTINCT TOP(1) space(32) + + '9' + space(2) +
CASE When cast(@Cnt as varchar(100)) is Not null then cast(@Cnt as varchar(100)) END +
space(2) +
CASE WHEN cast(Sys as varchar(100)) IS NOT NUll then cast(Sys as varchar(100)) END +
space(2) +
CASE WHEN cast(Prin as varchar(100)) IS NOT NUll then cast(Prin as varchar(100)) END +
space(22) +
CASE WHEN (Term_id) IS NOT NULL THEN Term_id END +
space(1) +
CASE WHEN (Op_Code) IS NOT NULL THEN Op_Code END
As MONDATA
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-06 : 14:39:32

You need to specify an ELSE condition for your CASE expressions, otherwise they return NULL if the condition is false.

Instead of CASE, you should just use COALESCE() to return a 0 length string ('') if the column is null -- read about it in books on line.

select coalesce(value1,'') + coalesce(value2,'') + ... etc ...

- Jeff
http://weblogs.sqlteam.com/JeffS

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-07 : 06:38:00

Thank so much to everyone who pitched in I figured it out yesterday (WHEW).
INSERT INTO tblExport
Select DISTINCT TOP(1) Space (32) + '9' + space(2) + CAST(@Cnt as varchar (5)) + space(2) +
CAST(Sys as varchar (4)) + space(2) +
CAST(Prin as varchar (4)) + space(22) +
'TEN' + space(1) + 'ZZ' As MONDATA
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin

Have a Great weekend I know I will.

OBTW. is there some way to give you guys acknowledgement for all the help and support you provide? For instance; programmer of the month, or most support provided for the month. Some way to say thanks with a big ol’ Atat-Boy thrown in. Without you guys we would be constantly re-inventing the wheel.
Be well,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-07 : 08:11:21

quote:
Originally posted by Trudye

Thank so much to everyone who pitched in I figured it out yesterday (WHEW).
INSERT INTO tblExport
Select DISTINCT TOP(1) Space (32) + '9' + space(2) + CAST(@Cnt as varchar (5)) + space(2) +
CAST(Sys as varchar (4)) + space(2) +
CAST(Prin as varchar (4)) + space(22) +
'TEN' + space(1) + 'ZZ' As MONDATA
FROM dbo.tblTrans
WHERE Sys = @vwSys AND Prin = @vwPrin

Have a Great weekend I know I will.

OBTW. is there some way to give you guys acknowledgement for all the help and support you provide? For instance; programmer of the month, or most support provided for the month. Some way to say thanks with a big ol’ Atat-Boy thrown in. Without you guys we would be constantly re-inventing the wheel.
Be well,

Just Thank you very much is enough

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources