Conversion failed when converting the varchar value '099.0' to data type int.

Question

Conversion failed when converting the varchar value '099.0' to data type int.

vsamuel

Mr or Mrs. 500

Points: 566
More actions
February 26, 2014 at 6:58 am

#304783

Thanks for your help peple

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 22999 More actions · Answer 1

vsamuel (2/26/2014)
I have this script below which initially could execute without problems. Now its giving me this error: "Conversion failed when converting the varchar value '099.0' to data type int.
"

I'm not sure but I think, '099.0' won't be recognised as an integer because of either the preceding 0 and\or the following decimal point. Int has to be a whole number.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Perhaps you could declare a variable as DECIMAL(10,2) then set the INT column to the value in the decimal variable... a 2 step conversion.

vsamuel Mr or Mrs. 500 Points: 566 More actions · Answer 3

vsamuel

Mr or Mrs. 500

Points: 566

February 26, 2014 at 7:42 am

#1692280

Many thanks, I have done that and its working

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You need to look up sql injection. Your code is pretty much a text book case of vulnerable to sql injection.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

I have a feeling too that you could make this entire thing into a few insert statements with no dynamic sql and no looping. It is so hard to read.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

I am pretty sure you could get rid of the dynamic sql at the very least. Isn't this this same thing for Check 1?

/* check 1 */

IF @Check1 is null

insert into Discrepancies

(

FormName,

IDNumber,

VisitDate,

ErrorDetail,

ErrVarName1,

Status

)

select @FormName,

@PatientID,

@VisitDate,

'enr_first cannot be null',

'enr_first',

'Open'

where not exists

(

select *

from Discrepancies

where FormName = @FormName

and IDNumber = @PatientID

and VisitDate = @VisitDate

and ErrorDetail = @ErrorDetail

)

It is hard to figure out exactly what your code is doing but I think the loop would be fairly easy to get rid of too.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Sean Lange (2/26/2014)
It is hard to figure out exactly what your code is doing but I think the loop would be fairly easy to get rid of too.

And now it's harder because the code is gone.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Luis Cazares (2/26/2014)
Sean Lange (2/26/2014)
It is hard to figure out exactly what your code is doing but I think the loop would be fairly easy to get rid of too.
And now it's harder because the code is gone.

Well I guess they "fixed" the issue and don't want to learn that the way they are doing it is far from optimal.

Too bad because this would be a great chance for the OP to learn a LOT about how to handle this type of thing.

I copied the original code before the OP removed it. I generally quote posts to avoid the code being removed. Unless the OP comes back and wants to fix the nightmarish dynamic sql I guess we will have to assume they are satisfied.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/