Stored Procedure was working fine but suddenly stopped for certain values

Question

So I've been working on a number of stored procedures for an SSRS report I'm building and have an odd error and need a pair of fresh eyes to see what I could be missing.

My procedure is pretty simple - SELECT various columns from some JOINed tables, INSERT them into a #temp table and SELECT all of the contents of the table to display as detail rows in my report.

My complete procedure is shown here:

USE [DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[rpt_select_ACHW_Ob]
      @PR_ID INT
AS BEGIN

SET NOCOUNT ON;

CREATE TABLE #temp
(BuildingNum INT,
MTHD VARCHAR(50),
ObDescript NVARCHAR(50),
SizeRemarks VARCHAR(50),
ObLength NUMERIC,
ObWidth NUMERIC,
ObArea NUMERIC,
Stories NUMERIC,
Grade VARCHAR(50),
YearBuilt SMALLINT,
Condition VARCHAR(50),
Phys NUMERIC,
FC VARCHAR(50),
PercentDone NUMERIC,
TaxValue NUMERIC)

DECLARE @RowCounter INT, @h INT
SET @RowCounter = 11

INSERT INTO #temp(BuildingNum, MTHD, ObDescript, SizeRemarks, ObLength, ObWidth, ObArea, Stories, Grade, YearBuilt, Condition, Phys, FC, PercentDone, TaxValue)
SELECT  ob.Ob_LineNumber AS BuildingNum,
        CASE WHEN ob.SP IS NOT NULL THEN 'S' ELSE 'P' END AS MTHD,
        som.Id AS ObDescript,
        CASE WHEN ob.SF IS NULL THEN ob.CN ELSE CAST((ob.SF + '/' + ob.CN) AS VARCHAR) END AS SizeRemarks,
        CASE WHEN ob.ob_Length IS NULL THEN 0 ELSE ob.Ob_Length END AS ObLength,
        CASE WHEN ob.ob_Width IS NULL THEN 0 ELSE ob.Ob_Width END AS ObWidth,
        CASE WHEN ob.ob_Length IS NULL WHEN ob.ob_Width IS NULL THEN 0 THEN 0 ELSE (ob.Ob_Length * ob.Ob_Width) END AS ObArea,
        ob.Ob_NStories AS OBStories,
        sovg.Grade AS ObGrade,
        ob.Ob_YearBuilt As ObYearBuilt,
        ob.Ob_ConditionCode AS ObConditionCode,
        ob.DR AS phys,
        ob.FC AS FC,
        ob.Ob_PercentComplete AS ObPercentComplete,
        ob.Ob_ValueTax AS TaxValue

    FROM        t_Ob ob WITH (NOLOCK)
    LEFT JOIN   t_ObToPR otpr WITH (NOLOCK) ON ob.Ob_ID=otpr.Ob_ID
    LEFT JOIN   t_PR pr WITH (NOLOCK) ON otpr.PR_ID=pr.PR_ID
    LEFT JOIN   t_S_Grade sovg WITH (NOLOCK) ON ob.S_Grade_ID=sovg.S_Grade_ID
    LEFT JOIN   t_SObD sod WITH (NOLOCK) ON ob.SObD_ID=sod.SObD_ID
    LEFT JOIN   t_SObM som WITH (NOLOCK) ON sod.SObM_ID=som.SObM_ID

WHERE       pr.PR_Id = @PR_ID

SET @h = (SELECT COUNT(*) FROM #temp) 
WHILE  @h < @RowCounter OR @h % @RowCounter > 0
BEGIN
    INSERT INTO #temp (BuildingNum) VALUES (NULL)
    SET @h = @h + 1
END

SELECT * FROM #temp 
ORDER BY CASE WHEN BuildingNum IS NULL THEN 1 ELSE 0 END, BuildingNum   
END

As I said, I've been having a strange issue with this code. It's been working fine for the past two weeks for all test cases. I'm using EXEC to select the records based on the parameter @PR_ID and it was working fine. Yesterday, after not having touched ANYTHING with the code, I've begun generating an error code for only certain PR_ID values:

Msg 8114, Level 16, State 5, Procedure rpt_select_ACHW_Ob, Line 28 [Batch Start Line 2]
Error converting data type varchar to numeric.

Line 28 leads you to FC VARCHAR(50) which I've checked 10 times. All of the data types declared in the #temp table match up perfectly with the values being selected. Does anyone have any ideas as to why this has stopped working?

Here's a dbFiddle link with some sample data

Currently working with SQL Server 2012.

Answer 1

One of two things happened. The less likely is your original query was written correctly and someone changed an underlying table -- changing an integer column to a string column -- and then populated the string column with non-numeric data.

The more likely scenario is that your original query has implicit conversion. This is just a problem waiting to happen -- and now you know why. You have an error message from SQL Server and it doesn't specify the table, the row, or the column where the problem occurs. Arrggg!

My suggestion is to go through the query and check every expression and comparison to be sure the types are compatible (number/number, string/string, datetime/datetime is sufficient). If they are not, add explicit conversions. You can add the conversions using try_convert() or try_cast(), which will at least avoid the error (at the expense of producing NULLs).

I wish SQL Server had a "no-implicit conversions" mode where it would warn you that a query was using such conversions. Alas, no. So, get into the habit of writing your queries so all conversions are explicit.

EDIT:

For instance (based on the comments), this expression:

CAST((ob.SF + '/' + ob.CN) AS VARCHAR

should be:

CAST( (ob.SF as VARCHAR(255)) + '/' + ob.CN) AS VARCHAR(255))

Note that you should include lengths in all CHAR()/VARCHAR() references in SQL Server.

Answer 2

Per your comment on @Gordon's answer: If accurate, mark Gordon's response as your solve.

This will fail:

Declare @SF Int = 12
Declare @CN VarChar(10) = '2'

Select CAST((@SF + '/' + @CN) AS VARCHAR)

Unless you add a Cast:

Select CAST((Cast(@SF As VarChar(10)) + '/' + @CN) AS VARCHAR)

Result

12/2