December 13, 2011 at 4:51 pm
I need help to anyone who can offer it. I inherited a server that runs several stored procedures each night. One failed for the first time ever over weekend. The code is below. The error i am getting is this
Error converting data type varchar to float. [SQLSTATE 42000] (Error 8114). The step failed.
I know that SP_PRICE field is not converting to Float correctly but i dont know how to fix it or find the bad records. The destination table is a character type of Float in the SP_PRICE field.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateSpecialPricingMBBV2_PROD4] AS BEGIN SET NOCOUNT ON;
TRUNCATE TABLE SpecialPricingMBBV2
INSERT INTO SpecialPricingMBBV2 ([SP_NUM],[PRODUCT_ID],[DESIGN_ID],[MODIFY_DATE],[USER_ID],[SERIAL_NUMBER],[REFERENCE_NUMBER],[CUSTOMER_NAME],[CUSTOMER_ID],[DESCRIPTION],[ORDERNUMBER],[SP_ID],[DESIGN_DESC],[DESIGN_STAT_ID],[DESIGN_STAT_DESC],[RD]) select * from OPENQUERY("HDC-C1PSQL01", 'SELECT f.design_input_val, a.product_id, a.design_id, a.modify_date, a.user_id, a.serial_number, null, null, null, null, null, substring(f.input_name,8,len(f.input_name)-7), a.design_desc, a.design_stat_cd, null,''ADD'' FROM concept.dbo.CO_DES_INPUT f inner join concept.dbo.CO_DES AS a ON f.design_id = a.design_id WHERE a.design_stat_cd in (select g.design_stat_cd from concept.dbo.co_des_status g where g.design_stat_seq_num > 99 and g.product_id = a.product_id) and f.input_name like ''SP_NUM_%''')
UPDATE a SET a.customer_name = b.customer_name, a.customer_id = b.ACCOUNT_NUMBER FROM SpecialPricingMBBV2 a JOIN "HDC-C1PSQL01".concept.dbo.CO_DES c on c.serial_number = a.serial_number JOIN "HDC-C1PSQL01".concept.dbo.CO_CUST b on b.ACCOUNT_NUMBER = c.CUSTOMER_ID
UPDATE a SET a.DESCRIPTION = b.DESCRIPTION FROM SpecialPricingMBBV2 a JOIN "HDC-C1PSQL01".concept.dbo.CO_DES_ITEM_MASTER b on b.DESIGN_ID = a.DESIGN_ID and a.PRODUCT_ID = b.PRODUCT_ID Where b.smartpart_num like ('MBB-'+ a.serial_number + '%')
UPDATE a SET a.ORDERNUMBER = b.ordernumber FROM SpecialPricingMBBV2 a JOIN "HDC-C1PSQL01".concept.dbo.CO_DOC b on a.SERIAL_NUMBER = b.ORDERREFNUMBER
UPDATE a SET a.DESIGN_STAT_DESC = b.DESIGN_STAT_DESC FROM SpecialPricingMBBV2 a JOIN "HDC-C1PSQL01".concept.dbo.CO_DES_STATUS b on a.DESIGN_STAT_ID = b.DESIGN_STAT_CD
TRUNCATE TABLE SpecialPricingCategoryMBBV2
insert SpecialPricingCategoryMBBV2 select * from OPENQUERY("HDC-C1PSQL01", 'select b.prodin_val from concept.dbo.co_prod_input a, concept.dbo.co_prod_input_value b where a.product_id = 197192010 and a.PRODIN_NAME = ''SP_CATEGORY_~~SPLPRICE~~'' and a.product_input_num = b.product_input_num and a.product_id = b.product_id')
TRUNCATE TABLE CO_DES_INPUT_QTY_MBBV2
insert CO_DES_INPUT_QTY_MBBV2 select * from OPENQUERY("HDC-C1PSQL01", 'select a.product_id,a.design_id,a.design_input_val as QTY from concept.dbo.co_des_input a JOIN concept.dbo.co_des b ON a.design_id=b.design_id and a.product_id = 197192010 and b.design_stat_cd in (select design_stat_cd from concept.dbo.co_des_status where design_stat_seq_num > 99 and product_id = 197192010) and a.input_name = ''SPECIAL_PRICING_QTY'' and a.DESIGN_INPUT_VAL <> ''0''')
DECLARE @PRODUCT_ID NUMERIC(18,0)
DECLARE @DESIGN_ID NUMERIC(18,0)
DECLARE @SQL NVARCHAR(2000)
TRUNCATE TABLE CO_DES_INPUT_MBBV2
DECLARE curCO_DES_INPUT_MBBV2 CURSOR FOR SELECT PRODUCT_ID, DESIGN_ID FROM CO_DES_INPUT_QTY_MBBV2
OPEN curCO_DES_INPUT_MBBV2
FETCH NEXT FROM curCO_DES_INPUT_MBBV2 INTO @PRODUCT_ID,@DESIGN_ID
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SQL = N'select a.product_id,a.user_id,a.design_id,a.product_input_num,a.input_name,a.input_type_cd,a.design_input_val,a.input_label,a.logic_id,a.seq_num,a.price,a.opt,a.updated
from "HDC-C1PSQL01".concept.dbo.co_des_input a JOIN "HDC-C1PSQL01".concept.dbo.co_des b ON a.design_id=b.design_id
and a.product_id = ''' + convert(varchar(50),@PRODUCT_ID) +
''' and a.design_id = ''' + convert(varchar(50),@DESIGN_ID) +
''' and b.design_stat_cd in (select design_stat_cd from "HDC-C1PSQL01".concept.dbo.co_des_status where design_stat_seq_num > 99 and product_id = 197192010) and a.design_input_val IS NOT NULL and a.input_name like ''SP*_%'' escape ''*'''
SET @SQL = N'insert CO_DES_INPUT_MBBV2 select * from openquery("HDC-C1PSQL01",''' + REPLACE(@SQL, '''', '''''') + ''')'
EXEC (@SQL)
FETCH NEXT FROM curCO_DES_INPUT_MBBV2 INTO @PRODUCT_ID,@DESIGN_ID
END
CLOSE curCO_DES_INPUT_MBBV2
DEALLOCATE curCO_DES_INPUT_MBBV2
DECLARE @SP_ID INT
DECLARE @SP_CATEGORY VARCHAR(50)
DECLARE @SP_CAT_DESCRIPTION VARCHAR(4000)
DECLARE @SP_DESCRIPTION VARCHAR(4000)
DECLARE @SP_PRICE VARCHAR(50)
DECLARE @SP_HIDDEN_FILE_NAME VARCHAR(1000)
DECLARE @SP_HIDDEN_FILE_VALUE VARCHAR(1000)
DECLARE @SP_REF_SPR_NUM VARCHAR(100)
DECLARE @SP_REF_JOB_NUM VARCHAR(100)
DECLARE @SP_REF_SPR_PRICE VARCHAR(50)
DECLARE @SP_RESPONSE VARCHAR(4000)
DECLARE @SP_ENG_HOURS VARCHAR(50)
DECLARE @SP_MAT_COSTS VARCHAR(50)
DECLARE @SP_LABOR_HOURS VARCHAR(50)
DECLARE @SP_CALCULATIONS VARCHAR(4000)
DECLARE curSpecialPricingMBBV2 CURSOR FOR SELECT PRODUCT_ID, DESIGN_ID, SP_ID FROM SpecialPricingMBBV2 WHERE RD = 'ADD'
OPEN curSpecialPricingMBBV2
FETCH NEXT FROM curSpecialPricingMBBV2 INTO @PRODUCT_ID,@DESIGN_ID,@SP_ID
WHILE @@FETCH_STATUS = 0 BEGIN
SET @SP_PRICE = '0'
SET @SQL = N'select @SP_PRICE = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_PRICE_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_PRICE varchar(50) output', @SP_PRICE output
SET @SP_CATEGORY = NULL
SET @SQL = N'select @SP_CATEGORY = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_CATEGORY_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_CATEGORY varchar(50) output', @SP_CATEGORY output
SET @SP_CAT_DESCRIPTION = NULL
SET @SQL = N'select @SP_CAT_DESCRIPTION = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_CAT_DESCRIPTION_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_CAT_DESCRIPTION varchar(4000) output', @SP_CAT_DESCRIPTION output
SET @SP_DESCRIPTION = NULL
SET @SQL = N'select @SP_DESCRIPTION = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_DESCRIPTION_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_DESCRIPTION varchar(4000) output', @SP_DESCRIPTION output
SET @SP_RESPONSE = NULL
SET @SQL = N'select @SP_RESPONSE = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_RESPONSE_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_RESPONSE varchar(4000) output', @SP_RESPONSE output
SET @SP_CALCULATIONS = NULL
SET @SQL = N'select @SP_CALCULATIONS = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_CALCULATIONS_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_CALCULATIONS varchar(4000) output', @SP_CALCULATIONS output
SET @SP_HIDDEN_FILE_NAME = NULL
SET @SQL = N'select @SP_HIDDEN_FILE_NAME = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_HIDDEN_FILE_NAME_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_HIDDEN_FILE_NAME varchar(1000) output', @SP_HIDDEN_FILE_NAME output
SET @SP_HIDDEN_FILE_VALUE = NULL
SET @SQL = N'select @SP_HIDDEN_FILE_VALUE = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_HIDDEN_FILE_VALUE_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_HIDDEN_FILE_VALUE varchar(1000) output', @SP_HIDDEN_FILE_VALUE output
SET @SP_REF_SPR_NUM = NULL
SET @SQL = N'select @SP_REF_SPR_NUM = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_REF_SPR_NUM_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_REF_SPR_NUM varchar(100) output', @SP_REF_SPR_NUM output
SET @SP_REF_JOB_NUM = NULL
SET @SQL = N'select @SP_REF_JOB_NUM = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_REF_JOB_NUM_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_REF_JOB_NUM varchar(100) output', @SP_REF_JOB_NUM output
SET @SP_REF_SPR_PRICE = NULL
SET @SQL = N'select @SP_REF_SPR_PRICE = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_REF_SPR_PRICE_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_REF_SPR_PRICE varchar(50) output', @SP_REF_SPR_PRICE output
SET @SP_ENG_HOURS = NULL
SET @SQL = N'select @SP_ENG_HOURS = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_ENG_HOURS_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_ENG_HOURS varchar(50) output', @SP_ENG_HOURS output
SET @SP_MAT_COSTS = NULL
SET @SQL = N'select @SP_MAT_COSTS = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_MAT_COSTS_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_MAT_COSTS varchar(50) output', @SP_MAT_COSTS output
SET @SP_LABOR_HOURS = NULL
SET @SQL = N'select @SP_LABOR_HOURS = design_input_val from CO_DES_INPUT_MBBV2 where product_id = ' + convert(nvarchar(20),@PRODUCT_ID) + ' and design_id = ' + convert(nvarchar(20),@DESIGN_ID) + ' and input_name = ''SP_LABOR_HOURS_' + convert(nvarchar(5),@SP_ID) + ''''
EXEC sp_executesql @SQL, N'@SP_LABOR_HOURS varchar(50) output', @SP_LABOR_HOURS output
UPDATE SpecialPricingMBBV2 SET
SP_PRICE = LTrim(RTrim(convert(FLOAT,@SP_PRICE))),
SP_CATEGORY = @SP_CATEGORY,
SP_CAT_DESCRIPTION = @SP_CAT_DESCRIPTION,
SP_DESCRIPTION = @SP_DESCRIPTION,
SP_HIDDEN_FILE_NAME_1 = @SP_HIDDEN_FILE_NAME,
SP_HIDDEN_FILE_VALUE_1 = @SP_HIDDEN_FILE_VALUE,
SP_REF_SPR_NUM = @SP_REF_SPR_NUM,
SP_REF_JOB_NUM = @SP_REF_JOB_NUM,
SP_REF_SPR_PRICE = @SP_REF_SPR_PRICE,
SP_RESPONSE = @SP_RESPONSE,
SP_ENG_HOURS = @SP_ENG_HOURS,
SP_MAT_COSTS = @SP_MAT_COSTS,
SP_LABOR_HOURS = @SP_LABOR_HOURS,
SP_CALCULATIONS = @SP_CALCULATIONS
WHERE CURRENT OF curSpecialPricingMBBV2
FETCH NEXT FROM curSpecialPricingMBBV2 INTO @PRODUCT_ID,@DESIGN_ID,@SP_ID
END
CLOSE curSpecialPricingMBBV2
DEALLOCATE curSpecialPricingMBBV2
DECLARE @SP_HIDDEN_FILE_NAME_1 VARCHAR(1000)
DECLARE @SP_HIDDEN_FILE_VALUE_1 VARCHAR(1000)
DECLARE @TEMP_NAME VARCHAR(1000)
DECLARE @TEMP_VALUE VARCHAR(1000)
DECLARE curSpecialPricingFile CURSOR FOR SELECT SP_HIDDEN_FILE_NAME_1, SP_HIDDEN_FILE_VALUE_1 FROM SpecialPricingMBBV2 WHERE SP_HIDDEN_FILE_NAME_1 IS NOT NULL
OPEN curSpecialPricingFile
FETCH NEXT FROM curSpecialPricingFile INTO @SP_HIDDEN_FILE_NAME_1, @SP_HIDDEN_FILE_VALUE_1
WHILE @@FETCH_STATUS = 0 BEGIN
SET @TEMP_NAME = @SP_HIDDEN_FILE_NAME_1
SET @TEMP_VALUE = @SP_HIDDEN_FILE_VALUE_1
IF (CHARINDEX(';',@TEMP_NAME) > 0) BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@SP_HIDDEN_FILE_NAME_1,1,CHARINDEX(';',@TEMP_NAME) - 1)
SET @TEMP_NAME = SUBSTRING(@TEMP_NAME, CHARINDEX(';',@TEMP_NAME) + 1, LEN(@TEMP_NAME) - LEN(SUBSTRING(@TEMP_NAME,1,CHARINDEX(';',@TEMP_NAME))))
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_NAME_1 = @SP_HIDDEN_FILE_NAME_1 WHERE CURRENT OF curSpecialPricingFile
IF (CHARINDEX(';',@TEMP_NAME) > 0) OR (LEN(@TEMP_NAME) > 0) BEGIN
IF (CHARINDEX(';',@TEMP_NAME) = 0) BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@TEMP_NAME,1,LEN(@TEMP_NAME))
SET @TEMP_NAME = ''
END ELSE BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@TEMP_NAME,1,CHARINDEX(';',@TEMP_NAME) - 1)
SET @TEMP_NAME = SUBSTRING(@TEMP_NAME, CHARINDEX(';',@TEMP_NAME) + 1, LEN(@TEMP_NAME) - LEN(SUBSTRING(@TEMP_NAME,1,CHARINDEX(';',@TEMP_NAME))))
END
--PRINT @SP_HIDDEN_FILE_NAME_1 + ':' + @TEMP_NAME
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_NAME_2 = @SP_HIDDEN_FILE_NAME_1 WHERE CURRENT OF curSpecialPricingFile
END
IF (CHARINDEX(';',@TEMP_NAME) > 0) OR (LEN(@TEMP_NAME) > 0) BEGIN
IF (CHARINDEX(';',@TEMP_NAME) = 0) BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@TEMP_NAME,1,LEN(@TEMP_NAME))
SET @TEMP_NAME = ''
END ELSE BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@TEMP_NAME,1,CHARINDEX(';',@TEMP_NAME) - 1)
SET @TEMP_NAME = SUBSTRING(@TEMP_NAME, CHARINDEX(';',@TEMP_NAME) + 1, LEN(@TEMP_NAME) - LEN(SUBSTRING(@TEMP_NAME,1,CHARINDEX(';',@TEMP_NAME))))
END
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_NAME_3 = @SP_HIDDEN_FILE_NAME_1 WHERE CURRENT OF curSpecialPricingFile
END
IF (CHARINDEX(';',@TEMP_NAME) > 0) OR (LEN(@TEMP_NAME) > 0) BEGIN
IF (CHARINDEX(';',@TEMP_NAME) = 0) BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@TEMP_NAME,1,LEN(@TEMP_NAME))
SET @TEMP_NAME = ''
END ELSE BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@TEMP_NAME,1,CHARINDEX(';',@TEMP_NAME) - 1)
SET @TEMP_NAME = SUBSTRING(@TEMP_NAME, CHARINDEX(';',@TEMP_NAME) + 1, LEN(@TEMP_NAME) - LEN(SUBSTRING(@TEMP_NAME,1,CHARINDEX(';',@TEMP_NAME))))
END
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_NAME_4 = @SP_HIDDEN_FILE_NAME_1 WHERE CURRENT OF curSpecialPricingFile
END
IF (CHARINDEX(';',@TEMP_NAME) > 0) OR (LEN(@TEMP_NAME) > 0) BEGIN
IF (CHARINDEX(';',@TEMP_NAME) = 0) BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@TEMP_NAME,1,LEN(@TEMP_NAME))
SET @TEMP_NAME = ''
END ELSE BEGIN
SET @SP_HIDDEN_FILE_NAME_1 = SUBSTRING(@TEMP_NAME,1,CHARINDEX(';',@TEMP_NAME) - 1)
SET @TEMP_NAME = SUBSTRING(@TEMP_NAME, CHARINDEX(';',@TEMP_NAME) + 1, LEN(@TEMP_NAME) - LEN(SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE))))
END
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_NAME_5 = @SP_HIDDEN_FILE_NAME_1 WHERE CURRENT OF curSpecialPricingFile
END
END
-- TEMP VALUE
IF (CHARINDEX(';',@TEMP_VALUE) > 0) BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@SP_HIDDEN_FILE_VALUE_1,1,CHARINDEX(';',@TEMP_VALUE) - 1)
SET @TEMP_VALUE = SUBSTRING(@TEMP_VALUE, CHARINDEX(';',@TEMP_VALUE) + 1, LEN(@TEMP_VALUE) - LEN(SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE))))
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_VALUE_1 = @SP_HIDDEN_FILE_VALUE_1 WHERE CURRENT OF curSpecialPricingFile
IF (CHARINDEX(';',@TEMP_VALUE) > 0) OR (LEN(@TEMP_VALUE) > 0) BEGIN
IF (CHARINDEX(';',@TEMP_VALUE) = 0) BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@TEMP_VALUE,1,LEN(@TEMP_VALUE))
SET @TEMP_VALUE = ''
END ELSE BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE) - 1)
SET @TEMP_VALUE = SUBSTRING(@TEMP_VALUE, CHARINDEX(';',@TEMP_VALUE) + 1, LEN(@TEMP_VALUE) - LEN(SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE))))
END
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_VALUE_2 = @SP_HIDDEN_FILE_VALUE_1 WHERE CURRENT OF curSpecialPricingFile
END
IF (CHARINDEX(';',@TEMP_VALUE) > 0) OR (LEN(@TEMP_VALUE) > 0) BEGIN
IF (CHARINDEX(';',@TEMP_VALUE) = 0) BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@TEMP_VALUE,1,LEN(@TEMP_VALUE))
SET @TEMP_VALUE = ''
END ELSE BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE) - 1)
SET @TEMP_VALUE = SUBSTRING(@TEMP_VALUE, CHARINDEX(';',@TEMP_VALUE) + 1, LEN(@TEMP_VALUE) - LEN(SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE))))
END
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_VALUE_3 = @SP_HIDDEN_FILE_VALUE_1 WHERE CURRENT OF curSpecialPricingFile
END
IF (CHARINDEX(';',@TEMP_VALUE) > 0) OR (LEN(@TEMP_VALUE) > 0) BEGIN
IF (CHARINDEX(';',@TEMP_VALUE) = 0) BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@TEMP_VALUE,1,LEN(@TEMP_VALUE))
SET @TEMP_VALUE = ''
END ELSE BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE) - 1)
SET @TEMP_VALUE = SUBSTRING(@TEMP_VALUE, CHARINDEX(';',@TEMP_VALUE) + 1, LEN(@TEMP_VALUE) - LEN(SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE))))
END
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_VALUE_4 = @SP_HIDDEN_FILE_VALUE_1 WHERE CURRENT OF curSpecialPricingFile
END
IF (CHARINDEX(';',@TEMP_VALUE) > 0) OR (LEN(@TEMP_VALUE) > 0) BEGIN
IF (CHARINDEX(';',@TEMP_VALUE) = 0) BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@TEMP_VALUE,1,LEN(@TEMP_VALUE))
SET @TEMP_VALUE = ''
END ELSE BEGIN
SET @SP_HIDDEN_FILE_VALUE_1 = SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE) - 1)
SET @TEMP_VALUE = SUBSTRING(@TEMP_VALUE, CHARINDEX(';',@TEMP_VALUE) + 1, LEN(@TEMP_VALUE) - LEN(SUBSTRING(@TEMP_VALUE,1,CHARINDEX(';',@TEMP_VALUE))))
END
UPDATE SpecialPricingMBBV2 SET SP_HIDDEN_FILE_VALUE_5 = @SP_HIDDEN_FILE_VALUE_1 WHERE CURRENT OF curSpecialPricingFile
END
END
FETCH NEXT FROM curSpecialPricingFile INTO @SP_HIDDEN_FILE_NAME_1, @SP_HIDDEN_FILE_VALUE_1
END
CLOSE curSpecialPricingFile
DEALLOCATE curSpecialPricingFile
Update SpecialPricingMBBv2 set RD='DEL' where RD = 'DSP'
Update SpecialPricingMBBv2 set RD='DSP' where RD = 'ADD'
Delete from SpecialPricingMBBv2 where RD = 'DEL'
END
December 14, 2011 at 2:27 pm
You can start by using the IsNumeric() function on the varchar column to see if there are values that are not recognized as numeric. As the link says, you need to be careful because some characters like "." and "$" are considered numeric, but may not be convertable to float.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 14, 2011 at 2:46 pm
Thanks, i tried that and it comes up empty. I have been debugging this all day. We cannot seem to find the bad record, and there are just too many to manually go through it. I usually stay away from Float character type for this reason. I rebuilt the stored procedure and the table and am using money and varchar values and it seem to work ok now. Thanks again.
December 14, 2011 at 2:54 pm
If it works with Money you likely have a "$" sign. You could do a like '%$%'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 14, 2011 at 2:57 pm
I could not find any $ in the data that is coming from the source and converting over to the destination table but I did try that and it did work so I think I am ok but I will stay away from the FLOAT characater type in future whenever possible. I inherited the procedure, but did not code it.
December 21, 2011 at 5:41 pm
suzanne.miller (12/14/2011)
I will stay away from the FLOAT characater type in future whenever possible.
FLOAT is not a character type, it's a numeric data type.
That's why this your statement is not appropriate:
LTrim(RTrim(convert(FLOAT,@SP_PRICE)))
You cannot trim numeric values.
You may start from reversing the order of the opetations:
convert(FLOAT, LTrim(RTrim(@SP_PRICE)))
But it might not help in 100% of cases.
You need to anayse data representation in the source table to understand what causes the failure.
From the name of the column (PRICE) I may assume "money" data type should be more useful here:
convert(FLOAT, convert(MONEY, LTrim(RTrim(@SP_PRICE))))
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply