Avatar of MBoy2
MBoy2
 asked on

SQL - SP This line allowing Non Int to get through

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
Microsoft SQL Server

Avatar of undefined
Last Comment
MBoy2

8/22/2022 - Mon
Jim Horn

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.
MBoy2

ASKER
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 Horn

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
MBoy2

ASKER
Now I get...

Error converting data type nvarchar to numeric.
8080_Diver

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.
MBoy2

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
MBoy2

ASKER
Any help would be appreciated.
MBoy2

ASKER
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',
ASKER CERTIFIED SOLUTION
8080_Diver

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MBoy2

ASKER
Thanks
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23