Try running this query if the error will not appear:

DECLARE @sql VARCHAR(1000)

DECLARE @TYPE_NAME NVARCHAR(100)

SET @sql = 'SELECT TYPE_NAME FROM TRACEBASE_NEW.dbo.TBL_TYPE_DEF WHERE TYPE_ID=''1'''

EXEC uspSingleResultFromSQLVarchar @sql,...

Try this query. 

SELECT  *,

   Tmp1 = Case WHEN Isnumeric(Name) = 1 THEN CAST(Name AS Numeric) ELSE 99999 END,

   Tmp2 = Case WHEN Isnumeric(Name) = 0 THEN Name ELSE...

Base on the error message, your trying to convert a nvarchar datatype value to an integer.

But on your stored procedure, there are no integer datatype defined.

On this query you should...

I think RGR'us' suggestion should be:

DECLARE @cus_no char(12)

DECLARE_cus_no char(12)

SET @cus_no = (SELECT next_cus_no FROM ARCTLFIL_SQL)

SET @next_cus_no = RIGHT(REPLICATE('0', 12) + CAST(@cus_no +...