SQL - SP This line allowing Non Int to get through

MBoy2
MBoy2 used Ask the Experts™
on
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'
                        END
Comment
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

Commented:
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.

Author

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

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[Mote_GetAllV1NestData]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[Mote_GetAllV1NestData];
GO
CREATE PROCEDURE [dbo].[Mote_GetAllV1NestData]
AS
BEGIN
     SELECT
     [ID] As 'OriginalNestID',
     'IsNest' = CASE [Nest]
                 WHEN 0 THEN '0'
                 WHEN 1 THEN '1'
                 Else '0'
                 END,
     'UnmarkedNest' = CASE [Verified]
                 WHEN 0 THEN '0'
                 WHEN 1 THEN '1'
                 Else '0'
                 END,      
     [Ft# So#] As 'FTNumberSONumber',                        
       'NestLocationDate' = CASE      WHEN ISDATE(Cast([Date] as datetime)) = 1
                        THEN CONVERT(VARCHAR(10), [Date], 101)
                        ELSE '01/01/1900'
                        END,
      (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,
      [Address],
      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'
                        END,
      'DistBarrier_FT' = CASE      WHEN Cast([Dist from Barrier(ft)] as INT) = 1
                        THEN [Dist from Barrier(ft)]
                        ELSE '0'
                        END,
      'HeightOfEscarpment_FT' = CASE      WHEN Cast([Height of Esc(ft)] as INT) = 1
                        THEN [Height of Esc(ft)]
                        ELSE '0'
                        END,
      'LengthOfEscarpment_FT' = CASE      WHEN Cast([Length of Esc(ft)] as INT) = 1
                        THEN [Length of Esc(ft)]
                        ELSE '0'
                        END,
      [Overall Comments] As 'NestLocationComments',
      [ActiveRecord] As 'Active'  
     FROM [V1Nest]
END
GO
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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'
                        END
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.

Author

Commented:
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.

Author

Commented:
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.

OLD...
                  [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,


NEW...

                 [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

Author

Commented:
Any help would be appreciated.

Author

Commented:
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.

Author

Commented:
Thanks

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