Mystical_Ice
asked on
SQL converting data problem (nvarchar to numeric)
Hi
Having a problem converting data. This is the query I'm using that's failing with Msg 8114 level 16 state 5: "Error converting data type nvarchar to numeric":
SELECT
--c.WorkOrderNumber as 'DBR Workorder Leg',
LEFT(c.WorkOrderNumber,LEN (c.WorkOrd erNumber)- 7) as 'COSS Workorder',
--d.CloseDate,
--a.ScheduledEndOperation,
a.ActualEndOperation as 'Machine Leg End',
--forgweight.datavalue as 'Forge Weight',
-- machweight.datavalue as 'Machine Weight',
--cast(ISNULL(forgweight.D ataValue,0 ) as decimal(10,3)) - cast(ISNULL(machweight.Dat aValue,0) as decimal(10,3)) as PoundsRemoved,
--e.ForgeWeight,
--e.MachineWeight,
--forgweight.datavalue as 'Forge Weight',
machweight.datavalue as 'Machine Weight',
forgweight.datavalue as 'Forge Weight',
ISNUMERIC(ISNULL(forgweigh t.DataValu e,0)) as 'FORGEVALUETEST',
ISNUMERIC(ISNULL(machweigh t.DataValu e,0)) as 'MACHVALUETEST',
CAST(ISNULL(forgweight.dat avalue,0) as numeric(15,5)) as 'Forge Weight',
CAST(ISNULL(machweight.dat avalue,0) as numeric(15,5)) as 'Machine Weight',
-- case when CAST(ISNULL(forgweight.dat avalue,0) as decimal(10,3)) > '1' then CAST(ISNULL(forgweight.dat avalue,0) as decimal(10,3)) - CAST(ISNULL(machweight.dat avalue,0) as decimal(10,3))
--else 0 end as PoundsRemoved,
CONVERT(CHAR(3), a.ActualEndOperation, 0) + ' ' + CAST(DATEPART(YEAR,a.Actua lEndOperat ion) as varchar) as MonthYear
-- case when e.ForgeWeight<e.MachineWei ght then '0' else e.forgeweight-e.machinewei ght end as PoundsRemoved
--e.ForgeWeight-e.MachineW eight as Pounds
--(CONVERT(INT,REPLACE(lef t(e.ForgeW eight, len(e.ForgeWeight) - charindex('.', reverse(e.ForgeWeight))),' ,','')) -
--CONVERT(INT,REPLACE(left (e.Machine Weight, len(e.MachineWeight) - charindex('.', reverse(e.MachineWeight))) ,',',''))) as PoundsRemoved
--e.ForgeWeight - e.MachineWeight as PoundsRemoved,
FROM [houdev\coss].DBROld.dbo.q ryRopeItem s a
JOIN [houdev\coss].DBROld.dbo.t blWorkorde rs c
on a.WorkOrderID = c.WorkOrderID
JOIN [houdev\coss].DBROld.dbo.t blWorkorde rStatus d
on c.WorkOrderID = d.WorkOrderID
--LEFT OUTER JOIN atf.dbo.COSS_vw_WorkOrders _Dimension s e
-- on LEFT(c.WorkOrderNumber,LEN (c.WorkOrd erNumber)- 7) = e.WorkOrderNumber
--on RIGHT(RTRIM(c.WorkOrderNum ber),11) = RIGHT(RTRIM(e.WorkOrderNum ber),11)
left outer join forg.dbo.coss_workorder e
on LEFT(c.WorkOrderNumber,LEN (c.WorkOrd erNumber)- 7) = RTRIM(e.UsrDfnId)
LEFT OUTER JOIN forg.dbo.COSS_CustomAttrib uteValue forgweight
on e.ident = forgweight.OwnerIdent and forgweight.AttribName = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.COSS_CustomAttrib uteValue machweight
on e.ident = machweight.OwnerIdent and machweight.AttribName = 'Machine Weight'
WHERE --a.OperationDescription = 'MACHINE SHOP COMPLETE' --and--
c.WorkOrderNumber like '%MACH' and
a.ResourceID = '2'
and (DATEADD(D, 0, DATEDIFF(D, 0, a.ActualEndOperation)) between '2014-06-01' and '2015-06-10')
--group by LEFT(c.WorkOrderNumber,LEN (c.WorkOrd erNumber)- 7), a.actualendoperation, forgweight.DataValue, machweight.DataValue
I can comment out: CAST(ISNULL(forgweight.dat avalue,0) as numeric(15,5)) as 'Forge Weight',
and if I do, the query runs without any issues.
I've tried running this to find out whether there's a non-numeric value in there, but I get no results:
SELECT
--c.WorkOrderNumber as 'DBR Workorder Leg',
LEFT(c.WorkOrderNumber,LEN (c.WorkOrd erNumber)- 7) as 'COSS Workorder',
--d.CloseDate,
--a.ScheduledEndOperation,
a.ActualEndOperation as 'Machine Leg End',
--forgweight.datavalue as 'Forge Weight',
-- machweight.datavalue as 'Machine Weight',
--cast(ISNULL(forgweight.D ataValue,0 ) as decimal(10,3)) - cast(ISNULL(machweight.Dat aValue,0) as decimal(10,3)) as PoundsRemoved,
--e.ForgeWeight,
--e.MachineWeight,
--forgweight.datavalue as 'Forge Weight',
machweight.datavalue as 'Machine Weight',
forgweight.datavalue as 'Forge Weight',
ISNUMERIC(ISNULL(forgweigh t.DataValu e,0)) as 'FORGEVALUETEST',
ISNUMERIC(ISNULL(machweigh t.DataValu e,0)) as 'MACHVALUETEST',
--CAST(ISNULL(forgweight.d atavalue,0 ) as numeric(15,5)) as 'Forge Weight',
CAST(ISNULL(machweight.dat avalue,0) as numeric(15,5)) as 'Machine Weight',
-- case when CAST(ISNULL(forgweight.dat avalue,0) as decimal(10,3)) > '1' then CAST(ISNULL(forgweight.dat avalue,0) as decimal(10,3)) - CAST(ISNULL(machweight.dat avalue,0) as decimal(10,3))
--else 0 end as PoundsRemoved,
CONVERT(CHAR(3), a.ActualEndOperation, 0) + ' ' + CAST(DATEPART(YEAR,a.Actua lEndOperat ion) as varchar) as MonthYear
-- case when e.ForgeWeight<e.MachineWei ght then '0' else e.forgeweight-e.machinewei ght end as PoundsRemoved
--e.ForgeWeight-e.MachineW eight as Pounds
--(CONVERT(INT,REPLACE(lef t(e.ForgeW eight, len(e.ForgeWeight) - charindex('.', reverse(e.ForgeWeight))),' ,','')) -
--CONVERT(INT,REPLACE(left (e.Machine Weight, len(e.MachineWeight) - charindex('.', reverse(e.MachineWeight))) ,',',''))) as PoundsRemoved
--e.ForgeWeight - e.MachineWeight as PoundsRemoved,
FROM [houdev\coss].DBROld.dbo.q ryRopeItem s a
JOIN [houdev\coss].DBROld.dbo.t blWorkorde rs c
on a.WorkOrderID = c.WorkOrderID
JOIN [houdev\coss].DBROld.dbo.t blWorkorde rStatus d
on c.WorkOrderID = d.WorkOrderID
--LEFT OUTER JOIN atf.dbo.COSS_vw_WorkOrders _Dimension s e
-- on LEFT(c.WorkOrderNumber,LEN (c.WorkOrd erNumber)- 7) = e.WorkOrderNumber
--on RIGHT(RTRIM(c.WorkOrderNum ber),11) = RIGHT(RTRIM(e.WorkOrderNum ber),11)
left outer join forg.dbo.coss_workorder e
on LEFT(c.WorkOrderNumber,LEN (c.WorkOrd erNumber)- 7) = RTRIM(e.UsrDfnId)
LEFT OUTER JOIN forg.dbo.COSS_CustomAttrib uteValue forgweight
on e.ident = forgweight.OwnerIdent and forgweight.AttribName = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.COSS_CustomAttrib uteValue machweight
on e.ident = machweight.OwnerIdent and machweight.AttribName = 'Machine Weight'
WHERE --a.OperationDescription = 'MACHINE SHOP COMPLETE' --and--
c.WorkOrderNumber like '%MACH' and
a.ResourceID = '2'
and (DATEADD(D, 0, DATEDIFF(D, 0, a.ActualEndOperation)) between '2014-06-01' and '2015-06-10')
--group by LEFT(c.WorkOrderNumber,LEN (c.WorkOrd erNumber)- 7), a.actualendoperation, forgweight.DataValue, machweight.DataValue
Out of ideas. Please let me know what you think
Having a problem converting data. This is the query I'm using that's failing with Msg 8114 level 16 state 5: "Error converting data type nvarchar to numeric":
SELECT
--c.WorkOrderNumber as 'DBR Workorder Leg',
LEFT(c.WorkOrderNumber,LEN
--d.CloseDate,
--a.ScheduledEndOperation,
a.ActualEndOperation as 'Machine Leg End',
--forgweight.datavalue as 'Forge Weight',
-- machweight.datavalue as 'Machine Weight',
--cast(ISNULL(forgweight.D
--e.ForgeWeight,
--e.MachineWeight,
--forgweight.datavalue as 'Forge Weight',
machweight.datavalue as 'Machine Weight',
forgweight.datavalue as 'Forge Weight',
ISNUMERIC(ISNULL(forgweigh
ISNUMERIC(ISNULL(machweigh
CAST(ISNULL(forgweight.dat
CAST(ISNULL(machweight.dat
-- case when CAST(ISNULL(forgweight.dat
--else 0 end as PoundsRemoved,
CONVERT(CHAR(3), a.ActualEndOperation, 0) + ' ' + CAST(DATEPART(YEAR,a.Actua
-- case when e.ForgeWeight<e.MachineWei
--e.ForgeWeight-e.MachineW
--(CONVERT(INT,REPLACE(lef
--CONVERT(INT,REPLACE(left
--e.ForgeWeight - e.MachineWeight as PoundsRemoved,
FROM [houdev\coss].DBROld.dbo.q
JOIN [houdev\coss].DBROld.dbo.t
on a.WorkOrderID = c.WorkOrderID
JOIN [houdev\coss].DBROld.dbo.t
on c.WorkOrderID = d.WorkOrderID
--LEFT OUTER JOIN atf.dbo.COSS_vw_WorkOrders
-- on LEFT(c.WorkOrderNumber,LEN
--on RIGHT(RTRIM(c.WorkOrderNum
left outer join forg.dbo.coss_workorder e
on LEFT(c.WorkOrderNumber,LEN
LEFT OUTER JOIN forg.dbo.COSS_CustomAttrib
on e.ident = forgweight.OwnerIdent and forgweight.AttribName = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.COSS_CustomAttrib
on e.ident = machweight.OwnerIdent and machweight.AttribName = 'Machine Weight'
WHERE --a.OperationDescription = 'MACHINE SHOP COMPLETE' --and--
c.WorkOrderNumber like '%MACH' and
a.ResourceID = '2'
and (DATEADD(D, 0, DATEDIFF(D, 0, a.ActualEndOperation)) between '2014-06-01' and '2015-06-10')
--group by LEFT(c.WorkOrderNumber,LEN
I can comment out: CAST(ISNULL(forgweight.dat
and if I do, the query runs without any issues.
I've tried running this to find out whether there's a non-numeric value in there, but I get no results:
SELECT
--c.WorkOrderNumber as 'DBR Workorder Leg',
LEFT(c.WorkOrderNumber,LEN
--d.CloseDate,
--a.ScheduledEndOperation,
a.ActualEndOperation as 'Machine Leg End',
--forgweight.datavalue as 'Forge Weight',
-- machweight.datavalue as 'Machine Weight',
--cast(ISNULL(forgweight.D
--e.ForgeWeight,
--e.MachineWeight,
--forgweight.datavalue as 'Forge Weight',
machweight.datavalue as 'Machine Weight',
forgweight.datavalue as 'Forge Weight',
ISNUMERIC(ISNULL(forgweigh
ISNUMERIC(ISNULL(machweigh
--CAST(ISNULL(forgweight.d
CAST(ISNULL(machweight.dat
-- case when CAST(ISNULL(forgweight.dat
--else 0 end as PoundsRemoved,
CONVERT(CHAR(3), a.ActualEndOperation, 0) + ' ' + CAST(DATEPART(YEAR,a.Actua
-- case when e.ForgeWeight<e.MachineWei
--e.ForgeWeight-e.MachineW
--(CONVERT(INT,REPLACE(lef
--CONVERT(INT,REPLACE(left
--e.ForgeWeight - e.MachineWeight as PoundsRemoved,
FROM [houdev\coss].DBROld.dbo.q
JOIN [houdev\coss].DBROld.dbo.t
on a.WorkOrderID = c.WorkOrderID
JOIN [houdev\coss].DBROld.dbo.t
on c.WorkOrderID = d.WorkOrderID
--LEFT OUTER JOIN atf.dbo.COSS_vw_WorkOrders
-- on LEFT(c.WorkOrderNumber,LEN
--on RIGHT(RTRIM(c.WorkOrderNum
left outer join forg.dbo.coss_workorder e
on LEFT(c.WorkOrderNumber,LEN
LEFT OUTER JOIN forg.dbo.COSS_CustomAttrib
on e.ident = forgweight.OwnerIdent and forgweight.AttribName = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.COSS_CustomAttrib
on e.ident = machweight.OwnerIdent and machweight.AttribName = 'Machine Weight'
WHERE --a.OperationDescription = 'MACHINE SHOP COMPLETE' --and--
c.WorkOrderNumber like '%MACH' and
a.ResourceID = '2'
and (DATEADD(D, 0, DATEDIFF(D, 0, a.ActualEndOperation)) between '2014-06-01' and '2015-06-10')
--group by LEFT(c.WorkOrderNumber,LEN
Out of ideas. Please let me know what you think
ASKER
Attaching a list of the output of the two columns when just pulling them and doing no conversion:
(
machweight.datavalue as 'Machine Weight',
forgweight.datavalue as 'Forge Weight',
)
I don't see any values in there that are causing it to fail
Book1.xlsx
(
machweight.datavalue as 'Machine Weight',
forgweight.datavalue as 'Forge Weight',
)
I don't see any values in there that are causing it to fail
Book1.xlsx
E.g.
SELECT *
FROM [houdev\coss].dbrold.dbo.qryropeitems a
JOIN [houdev\coss].dbrold.dbo.tblworkorders c
ON a.workorderid = c.workorderid
JOIN [houdev\coss].dbrold.dbo.tblworkorderstatus d
ON c.workorderid = d.workorderid
LEFT OUTER JOIN forg.dbo.coss_workorder e ON LEFT(c.workordernumber, LEN(c.workordernumber) - 7) = RTRIM(e.usrdfnid)
LEFT OUTER JOIN forg.dbo.coss_customattributevalue forgweight ON e.ident = forgweight.ownerident
AND forgweight.attribname = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.coss_customattributevalue machweight ON e.ident = machweight.ownerident
AND machweight.attribname = 'Machine Weight'
WHERE c.workordernumber LIKE '%MACH'
AND a.resourceid = '2'
AND DATEADD(D, 0, DATEDIFF(d, 0, a.actualendoperation)) BETWEEN '2014-06-01' AND '2015-06-10'
AND ISNUMERIC(forgweight.datavalue) = 0;
You've got a non-null value somewhere that cannot be converted to a numeric type. If you are on SQL Server 2012 or later, you can try something like this to find it:
SELECT *
FROM forgweight
WHERE TRY_PARSE(datavalue AS decimal(15, 5) IS NULL
ASKER
I attached the values above in an excel file. Don't see anything that could be causing it.
ASKER
Also, this is on SQL 2008R2
What does my query return?
ASKER
Retrieves no data
And
SELECT CAST(ISNULL(forgweight.datavalue,0) AS NUMERIC(15,5))
FROM [houdev\coss].dbrold.dbo.qryropeitems a
JOIN [houdev\coss].dbrold.dbo.tblworkorders c ON a.workorderid = c.workorderid
JOIN [houdev\coss].dbrold.dbo.tblworkorderstatus d ON c.workorderid = d.workorderid
LEFT OUTER JOIN forg.dbo.coss_workorder e ON LEFT(c.workordernumber, LEN(c.workordernumber) - 7) = RTRIM(e.usrdfnid)
LEFT OUTER JOIN forg.dbo.coss_customattributevalue forgweight ON e.ident = forgweight.ownerident
AND forgweight.attribname = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.coss_customattributevalue machweight ON e.ident = machweight.ownerident
AND machweight.attribname = 'Machine Weight'
WHERE c.workordernumber LIKE '%MACH'
AND a.resourceid = '2'
AND DATEADD(D, 0, DATEDIFF(d, 0, a.actualendoperation)) BETWEEN '2014-06-01' AND '2015-06-10'
AND ISNUMERIC(ISNULL(forgweight.datavalue,0)) = 1;
ASKER
Msg 8114, Level 16, State 5, Line 3
Error converting data type nvarchar to numeric.
Error converting data type nvarchar to numeric.
Then there is really a value which is considered to be numeric, but it's not convertible. This may happen, cause the ISNUMERIC() and ISDATE() functions are pretty liberal when testing strings.
The TRY_PARSE(), TRY_CAST(), TRY_CONVERT() methods are better, cause they really try to do the conversion first, but throw no error.
So you need to test your data in batches to find the problematic value.
E.g.
Do a binary search. Thus start with ROWS_PER_PAGE half the number of distinct rows in the Data CTE. then page through it. When an exception is thrown, half the ROWS_PER_PAGE value and page through the problem page.
The TRY_PARSE(), TRY_CAST(), TRY_CONVERT() methods are better, cause they really try to do the conversion first, but throw no error.
So you need to test your data in batches to find the problematic value.
E.g.
DECLARE @ROWS_PER_PAGE AS INT;
SET @ROWS_PER_PAGE = 1000;
DECLARE @PageNumber AS INT;
SET @PageNumber = 1;
WITH Data
AS ( SELECT DISTINCT
forgweight.datavalue
FROM [houdev\coss].dbrold.dbo.qryropeitems a
JOIN [houdev\coss].dbrold.dbo.tblworkorders c ON a.workorderid = c.workorderid
JOIN [houdev\coss].dbrold.dbo.tblworkorderstatus d ON c.workorderid = d.workorderid
LEFT OUTER JOIN forg.dbo.coss_workorder e ON LEFT(c.workordernumber, LEN(c.workordernumber) - 7) = RTRIM(e.usrdfnid)
LEFT OUTER JOIN forg.dbo.coss_customattributevalue forgweight ON e.ident = forgweight.ownerident
AND forgweight.attribname = 'Forge Weight'
LEFT OUTER JOIN forg.dbo.coss_customattributevalue machweight ON e.ident = machweight.ownerident
AND machweight.attribname = 'Machine Weight'
WHERE c.workordernumber LIKE '%MACH'
AND a.resourceid = '2'
AND DATEADD(D, 0, DATEDIFF(d, 0, a.actualendoperation)) BETWEEN '2014-06-01' AND '2015-06-10'
AND ISNUMERIC(ISNULL(forgweight.datavalue, 0)) = 1
),
Ordered
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY D.datavalue ) AS RN ,
D.datavalue
FROM Data D
)
SELECT O.datavalue ,
CAST(ISNULL(O.datavalue, 0) AS NUMERIC(15, 5))
FROM Ordered O
WHERE O.RN BETWEEN ( ( @PageNumber - 1 ) * @ROWS_PER_PAGE + 1 ) AND ( @PageNumber * @ROWS_PER_PAGE )
ORDER BY O.datavalue;
Do a binary search. Thus start with ROWS_PER_PAGE half the number of distinct rows in the Data CTE. then page through it. When an exception is thrown, half the ROWS_PER_PAGE value and page through the problem page.
ASKER
That script gives me the error: "Incorrect syntax near Ordered"
hmm, should work. Are you really on 2008R2 ?
Use IsNumeric in case statement & I alway use try catch on all sql:
BEGIN TRY
Select
CAST(
CASE
WHEN IS NULL Value
THEN 0
WHEN LEN(forgweight) = 1 AND ISNUMERIC(VALUE + '.e0') = 1 -- decimal
THEN VALUE
WHEN LEN(forgweight) > 1 AND ISNUMERIC(Value + '.0e0') = 1 -- integer
THEN VALUE
ELSE 0 END AS as numeric(15,5)) From forgweight
SELECT
-- I wrap the error statement in a stored proc .... See below
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END TRY
BEGIN CATCH
SELECT
-- I wrap the error statement in a stored proc .... See below
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
BUT I would first run the following to do a quick check and find the first row that errors this will give you the rownum
BEGIN TRY
SELECT CAST(ISNULL(forgweight.dat avalue,0) as numeric(15,5)) From forgweight
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
-- Instead of a select I insert this info into a error table
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
-------------------------- ---------- ---------- ---------
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
BEGIN TRY
Select
CAST(
CASE
WHEN IS NULL Value
THEN 0
WHEN LEN(forgweight) = 1 AND ISNUMERIC(VALUE + '.e0') = 1 -- decimal
THEN VALUE
WHEN LEN(forgweight) > 1 AND ISNUMERIC(Value + '.0e0') = 1 -- integer
THEN VALUE
ELSE 0 END AS as numeric(15,5)) From forgweight
SELECT
-- I wrap the error statement in a stored proc .... See below
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END TRY
BEGIN CATCH
SELECT
-- I wrap the error statement in a stored proc .... See below
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
BUT I would first run the following to do a quick check and find the first row that errors this will give you the rownum
BEGIN TRY
SELECT CAST(ISNULL(forgweight.dat
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
-- Instead of a select I insert this info into a error table
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
--------------------------
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
GO
Opps... hopefully obvious but you'll have to change "Value" to forgweigh and I would put the case statement into a user funtion or scalar function <-- I prefer scalar.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
CAST(ISNULL(forgweight.dat
I have also tried:
CAST(ISNULL(forgweight.dat