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.
Author |
Topic |
Niladri20052006
Starting Member
4 Posts |
Posted - 2013-05-03 : 03:33:17
|
Hi All,I have written a query and it is running smoothly.quote:
select sum((case when isnumeric(amount) = 1 then cast(amount as float) end)) as Amount from filetransaction where fileid =333 AND iscontra=0But when I am pasting the same in SP it shows me the errorMsg 8114, Level 16, State 5, Procedure bacs_list, Line 17Error converting data type varchar to float.Could u please guide me!!Niladri Sekhar Biswas |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-03 : 04:09:48
|
isnumeric is not fully reliable. you may be better off using logic likeselect sum(cast(amount as float)) as Amount from filetransaction where fileid =333 AND iscontra=0 and amount NOT LIKE '%[^0-9]%' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-03 : 04:10:54
|
check this and see why isnumeric is not reliableSELECT ISNUMERIC('$'),ISNUMERIC('1e4')------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|