Conversion failed when converting the nvarchar value 'N/A' to data type int
Well, this is pretty self explanatory; SQL was expecting an INTeger value and instead it got an NVARCHAR.
My guess would be that your source table has the PipeLen
field defined as an NVARCHAR and that one of the rows is populated with "N/A"
If this indeed the case... the way to fix it is to check to see if that field can be Parsed
as an INT before you try to do math with it.
In SQL Server, this would be done using the
function; which will return a NULL
if it cannot be parsed.
I greatly abbreviated what I think you have for a structure into just the basics, and put this proof of concept together. You naturally will need to tailor it into your own SQL on your own.
DECLARE @Temp TABLE (RowID INT IDENTITY (1,1), PipeLen NVARCHAR(20))
VALUES ('12'), ('18'), ('24'), ('33'), ('n/a')
, PipeMiles = CASE WHEN TRY_PARSE(PipeLen as INT) IS NULL
ELSE Convert(NVARCHAR(20), PipeLen /12)
+ Convert(NVARCHAR(20) ,PipeLen %12)+'"'
which returns this recordset
RowID PipeLen PipeMiles
----- ------- ---------
1 12 1'0"
2 18 1'6"
3 24 2'0"
4 33 2'9"
5 n/a n/a
1. TRY_PARSE (Transact-SQL) - SQL Server | Microsoft Docs
2. SQL Server TRY_PARSE() Function Explained By Examples