SQL - SP This line allowing Non Int to get through

MBoy2 used Ask the Experts™
This fails because data 3.5 won't convert to Int.  Why is 3.5 making it through this case?

'LengthOfEscarpment_FT' = CASE WHEN Cast([Length of Esc(ft)] as INT) = 1
                        THEN [Length of Esc(ft)]
                        ELSE '0'
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

For starters, what is the data type of [Length of Esc(ft)]?
If it's some kind of text, use CAST('3.5' as numeric(19,4))

Int = Integer = Number with zero decimal places.
3.5 = one decimal place.  fyi.

btw Length of Esc(ft) is not a good name for a column, as the parentheses marks forces you to use square brackets [ ] to refer to it in T-SQL.


I'm just looking for a quick answer if available.. here's the complete SP

DROP PROCEDURE [dbo].[Mote_GetAllV1NestData];
CREATE PROCEDURE [dbo].[Mote_GetAllV1NestData]
     [ID] As 'OriginalNestID',
     'IsNest' = CASE [Nest]
                 WHEN 0 THEN '0'
                 WHEN 1 THEN '1'
                 Else '0'
     'UnmarkedNest' = CASE [Verified]
                 WHEN 0 THEN '0'
                 WHEN 1 THEN '1'
                 Else '0'
     [Ft# So#] As 'FTNumberSONumber',                        
       'NestLocationDate' = CASE      WHEN ISDATE(Cast([Date] as datetime)) = 1
                        THEN CONVERT(VARCHAR(10), [Date], 101)
                        ELSE '01/01/1900'
      (Select [Mote_Observer].[ObserverID] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverName] = [V1Nest].[Observer]) AS ObserverID,
      (Select [Mote_Observer].[ObserverID] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverName] = 'Unknown Observer') AS Observer2ID,
      (Select [Mote_Observer].[ObserverID] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverName] = 'Unknown Observer') AS Observer3ID,
      (Select [Mote_Observer].[ObserverID] FROM [Mote_Observer] WHERE [Mote_Observer].[ObserverName] = 'Unknown Observer') AS Observer4ID,
      [Nest/FC No#] As 'NestFCNumber',
      (Select [Mote_Key].[KeyID] FROM [Mote_Key] WHERE [Mote_Key].[KeyName] = [V1Nest].[Key]) AS KeyID,
      (Select [Mote_Zone].[ZoneID] FROM [Mote_Zone] WHERE [Mote_Zone].[ZoneName] = [V1Nest].[Zone]) AS ZoneID,
      (Select [Mote_DnrMonumentNumber].[DnrMonumentNumberID] FROM [Mote_DnrMonumentNumber] WHERE [Mote_DnrMonumentNumber].[DnrMonumentNumber] = [V1Nest].[DNR Monument #]) AS DnrMonumentNumberID,
      Select [Mote_County].[CountyID] FROM [Mote_County] WHERE [Mote_County].[CountyName] = 'Unknown County') AS ObserverID,
      (Select [Mote_CondoHotelPark].[CondoHotelParkID] FROM [Mote_CondoHotelPark] WHERE [Mote_CondoHotelPark].[CondoHotelParkName] = [V1Nest].[Condo/Resort/Park  Name]) AS CondoHotelParkID,
      (Select [Mote_Species].[SpeciesID] FROM [Mote_Species] WHERE [Mote_Species].[SpeciesName] = [V1Nest].[Species]) AS SpeciesID,
      [Latitude] As 'NestLatitude',
      [Longitude] As 'NestLongitude',
      (Select [Mote_BeachCategory].[BeachCategoryID] FROM [Mote_BeachCategory] WHERE [Mote_BeachCategory].[BeachCategoryName] = [V1Nest].[Beach Category]) AS BeachCategoryID,
      [Initial Surface2Eggs(cm)] As 'InitialSurface2Eggs',
      [NNE - # Body Pits] As 'NneNumberBodyPits',
      [NNE - # Abandon Cavity] As 'NneNumberAbandonCavity',
      [Obstructions] As 'Obstructions',
      (Select [Mote_EscarpmentType].[EscarpmentTypeID] FROM [Mote_EscarpmentType] WHERE [Mote_EscarpmentType].[EscarpmentTypeName] = [V1Nest].[Type Esc(Sloped/Vertical)]) AS EscarpmentTypeID,
      'DistMHW_FT' = CASE      WHEN Cast([Dist from MHW(ft)] as INT) = 1
                        THEN [Dist from MHW(ft)]
                        ELSE '0'
      'DistBarrier_FT' = CASE      WHEN Cast([Dist from Barrier(ft)] as INT) = 1
                        THEN [Dist from Barrier(ft)]
                        ELSE '0'
      'HeightOfEscarpment_FT' = CASE      WHEN Cast([Height of Esc(ft)] as INT) = 1
                        THEN [Height of Esc(ft)]
                        ELSE '0'
      'LengthOfEscarpment_FT' = CASE      WHEN Cast([Length of Esc(ft)] as INT) = 1
                        THEN [Length of Esc(ft)]
                        ELSE '0'
      [Overall Comments] As 'NestLocationComments',
      [ActiveRecord] As 'Active'  
     FROM [V1Nest]
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Quick?  Replace the offending block with this...

'LengthOfEscarpment_FT' = CASE WHEN Cast([Length of Esc(ft)] as numeric(19,4)) = 1
                        THEN [Length of Esc(ft)]
                        ELSE '0'
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.


Now I get...

Error converting data type nvarchar to numeric.
I suspect that your original problem is not exactly what you think it is.  The problem with converting your column to an integer is occuring in the CAST function and not in the assignment of the value to the more properly named column.

Using the [Length of Esc(ft)] column name is bad enough but to also have a numeric value stored in an nvarchar column is just compounding your sins.  This problem is just one of the reasons why you shouldn't do that.


If you look at the flow of the SP you can see that I am converting a database designed by an idiot into a database designed by a professional.  I'm using a mix of C# code and stored procedures to move 51K records into a normalized design.  I'm also donating my time on this one.

                  [Emergence No#] [nvarchar](12) NULL,
      [Key] [nvarchar](4) NULL,
      [Date] [date] NULL,
      [Observer] [nvarchar](100) NULL,
      [Zone] [nvarchar](6) NULL,
      [Species] [nvarchar](4) NULL,
      [Nest] [nvarchar](2) NULL,
      [Verified] [nvarchar](1) NULL,
      [Unmarked Nest] [bit] NOT NULL,
      [Initial Surface2Eggs(cm)] [int] NULL,
      [Nest/FC No#] [nvarchar](25) NULL,
      [Dist from MHW(ft)] [int] NULL,
      [Dist from Barrier(ft)] [int] NULL,
      [NNE - # Body Pits] [int] NULL,
      [NNE - # Abandon Cavity] [int] NULL,
      [Address] [nvarchar](52) NULL,
      [Condo/Resort/Park  Name] [nvarchar](50) NULL,
      [DNR Monument #] [int] NULL,
      [Ft# So#] [int] NULL,
      [Latitude] [float] NULL,
      [Longitude] [float] NULL,
      [Beach Category] [nvarchar](13) NULL,
      [Obstructions] [nvarchar](250) NULL,
      [Type Esc(Sloped/Vertical)] [nvarchar](15) NULL,
      [Crawled Over Esc] [nvarchar](50) NULL,
      [Height of Esc(ft)] [nvarchar](5) NULL,
      [Length of Esc(ft)] [nvarchar](5) NULL,
      [PIT Tag #] [nvarchar](50) NULL,
      [LFF Tag #] [nvarchar](50) NULL,
      [RFF Tag #] [nvarchar](50) NULL,
      [Turtle Number] [int] NULL,
      [Comments] [nvarchar](225) NULL,
      [Caged/Screened] [nvarchar](3) NULL,
      [Date Caged/Screen] [date] NULL,
      [Type Cage/Screen] [nvarchar](3) NULL,
      [Relocated] [nvarchar](3) NULL,
      [Date Relocated] [date] NULL,
      [Relocator] [nvarchar](100) NULL,
      [Why Moved] [nvarchar](32) NULL,
      [# Eggs Moved] [float] NULL,
      [# Eggs Relocator Broke] [float] NULL,
      [New Location] [nvarchar](33) NULL,
      [Relocated Latitude] [float] NULL,
      [Relocated Longitude] [float] NULL,
      [Initial Treatment] [nvarchar](1) NULL,
      [Final Treatment] [nvarchar](1) NULL,
      [Predated] [nvarchar](1) NULL,
      [Date(s) Predated] [nvarchar](250) NULL,
      [Predator(s)] [nvarchar](250) NULL,
      [Root Damage (0 if none)] [nvarchar](20) NULL,
      [# Eggs Damaged by Pred] [float] NULL,
      [Vandalized] [nvarchar](3) NULL,
      [Date(s) Vandalized] [nvarchar](25) NULL,
      [Type of Vandalism] [nvarchar](25) NULL,
      [Poached] [nvarchar](3) NULL,
      [Date(s) Poached] [nvarchar](25) NULL,
      [Were Eggs Removed] [nvarchar](3) NULL,
      [Nest Washed Over] [nvarchar](3) NULL,
      [Date(s) Washed Over] [nvarchar](255) NULL,
      [Storm Name Washed Over] [nvarchar](25) NULL,
      [Nest Washed Out] [nvarchar](3) NULL,
      [Date(s) Washed Out] [nvarchar](25) NULL,
      [Storm Name Washed Out] [nvarchar](25) NULL,
      [Drop Date] [date] NULL,
      [Date Hatched] [date] NULL,
      [Add_l Hatch Date(s)] [nvarchar](10) NULL,
      [Disorientation Date(s)] [nvarchar](15) NULL,
      [Date Excavated] [date] NULL,
      [Excavated By] [nvarchar](100) NULL,
      [Live in Nest] [float] NULL,
      [Dead in Nest] [float] NULL,
      [Final Surface to Eggs(cm)] [float] NULL,
      [Depth to Bottom(cm)] [float] NULL,
      [Cavity Width(cm)] [float] NULL,
      [# Hatched] [float] NULL,
      [# Live pipped] [float] NULL,
      [# Dead pipped] [float] NULL,
      [# Eggs Destroyed] [float] NULL,
      [# Unhatched] [float] NULL,
      [Total Eggs] [float] NULL,
      [Overall Comments] [nvarchar](255) NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [FWC Diso #] [nvarchar](50) NULL,


                 [NestLocationID] [int] IDENTITY(1,1) Primary Key NOT NULL,
      [NestLocationName] [nvarchar](50) NULL, --calculated field
      [OriginalNestID] [int] NOT NULL,
      [IsNest]  [Bit] NOT NULL,
      [UnmarkedNest]  [Bit] Default(0) NOT NULL,
      [FTNumberSONumber] [int] NULL,       
      [NestLocationDate] DATETIME NOT NULL,
      [ObserverID] [int] NOT NULL,
      [Observer2ID] [int] NOT NULL,
      [Observer3ID] [int] NOT NULL,
      [Observer4ID] [int] NOT NULL,
      [NestFCNumber] [nvarchar](50) NULL,                    
      [KeyID] [int] NOT NULL,
      [ZoneID] [int] NOT NULL,
      [DnrMonumentNumberID] [int] NULL,
      [Address] [nvarchar](50) NULL,
      [CountyID] [int] NOT NULL,      
      [CondoHotelParkID] [int] Default(0) NOT NULL,
      [SpeciesID] [int] NOT NULL,
      [NestLatitude] [FLOAT] NULL,
      [NestLongitude] [FLOAT] NULL,
      [BeachCategoryID] [int] NULL,
      [InitialSurface2Eggs] [nvarchar](50) NULL,  
      [NneNumberBodyPits] [nvarchar](50) NULL,  
      [NneNumberAbandonCavity] [nvarchar](50) NULL,      
      [Obstructions] [nvarchar](50) NULL,
      [EscarpmentTypeID] [int] NULL,
      [DistMHW_FT] [int] NULL,
      [DistBarrier_FT] [int] NULL,
      [HeightOfEscarpment_FT] [int] NULL,
      [LengthOfEscarpment_FT] [int] NULL,
      [NestLocationComments] [nvarchar](500) NULL,
      [Active] [bit] DEFAULT(1) NOT NULL


Any help would be appreciated.


Is there a way to change this line so we test for NULL and return a 1

(Select [Mote_BeachCategory].[BeachCategoryID] FROM [Mote_BeachCategory] WHERE [Mote_BeachCategory].[BeachCategoryName] = [V1Nest].[Beach Category]) As 'BeachCategoryID',
If you want to change a NULL into a 1, you can use ISNULL(columnname, 1) and you can do that for as many columns as you want to in your SELECT query.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial