SQL Server 2005 stored procedure error - Line numbers don't match management studio

Question

I have a stored procedure that emits the following error.

Msg 8115, Level 16, State 2, Procedure procPrptRiskTrendByIndustry, Line 33
Arithmetic overflow error converting expression to data type datetime.

How do I know which line the error occurs at? Line 33 is the obvious answer, but line 33 in SQL Server Management Studio does not match the error in the stored procedure.

I have include the stored procedure below, but my question is about the line numbers not match.

ALTER PROCEDURE [dbo].[procPrptRiskTrendByIndustry] 
    @subid INT,
    @portfolio VARCHAR(500),
    @currentDateKey INT ,
    @priorDateKey INT ,
    @exposureFrom INT ,
    @exposureTo INT,
    @industry VARCHAR(500),
    @status VARCHAR(500)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
-- Create Filter Table '##Portfolio_Filter' With data in @portfolio
EXEC procCreateFilterTbl N'##Portfolio_Filter' , @portfolio;

-- Create Filter Table '##Industry_Filter' With data in @industry
EXEC procCreateFilterTbl N'##Industry_Filter' , @industry;

-- Create Filter Table '##Status_Filter' With data in @status
EXEC procCreateFilterTbl N'##Status_Filter' , @status;

--- Main Query
SELECT
CASE WHEN naics_cd is NULL THEN 'Unclassified'
  ELSE naics_short + ' (' + CAST(naics_cd AS varchar(3)) + ')' END AS 'naics',

SUM (CASE WHEN as_of_date=@currentDateKey THEN 1 ELSE 0 END) AS 'pop_cur',
SUM (CASE WHEN as_of_date=@currentDateKey THEN ISNULL([cur_bal_member_lender], 0) ELSE 0 END)/1000 AS 'exposure_cur',
SUM (CASE WHEN as_of_date=@currentDateKey THEN ISNULL([PayNet_absolutepd_4q] * [cur_bal_member_lender], 0) ELSE 0 END)/1000 AS 'expWtdPD_cur',
SUM (CASE WHEN as_of_date=@priorDateKey THEN 1 ELSE 0 END) AS 'pop_pp',
SUM (CASE WHEN as_of_date=@priorDateKey THEN ISNULL([cur_bal_member_lender], 0) ELSE 0 END)/1000 AS 'exposure_pp',
SUM (CASE WHEN as_of_date=@priorDateKey THEN ISNULL([PayNet_absolutepd_4q] * [cur_bal_member_lender], 0) ELSE 0 END)/1000 AS 'expWtdPD_pp'
  FROM apd_fact
INNER JOIN dbo.APD_Portfolio_Mapping AS apm
    ON dbo.APD_Fact.SubID = apm.SubID
    AND dbo.APD_Fact.Portfolio = apm.Portfolio
INNER JOIN ##Portfolio_Filter AS pf
    ON apm.Mapped_Portfolio_ID = pf.FilterId
INNER JOIN ##Industry_Filter AS inf
    ON apd_fact.industry_segment = inf.FilterId
LEFT OUTER JOIN [naics_lookup]
      on LEFT([naics_code],3)=[naics_cd]
INNER JOIN ( SELECT customer_number ,
                            Portfolio ,
                            CASE WHEN COUNT(*) = 2 THEN 'ACTIVE'
                                 WHEN MAX(as_of_date_key) = @currentDateKey
                                 THEN 'NEW'
                                 WHEN MAX(as_of_date_key) = @priorDateKey
                                 THEN 'CLOSED'
                            END AS 'borrower_status'
                     FROM   APD_Fact
                     WHERE  APD_Fact.SubID = @subid
                            AND as_of_date_key IN ( @currentDateKey, @priorDateKey)
                     GROUP BY customer_number ,
                            Portfolio
                   ) AS b ON APD_Fact.customer_number = b.customer_number
                             AND APD_Fact.Portfolio = b.Portfolio
INNER JOIN ##Status_Filter AS sf
    ON b.borrower_status = sf.FilterId
WHERE APD_Fact.SubID=@subid
    AND as_of_date in (@currentDateKey,@priorDateKey)
    AND [cur_bal_member_lender] BETWEEN @exposureFrom AND @exposureTo
GROUP BY naics_cd, naics_short
ORDER BY 4 DESC, 7 DESC, 2 ASC ;
END

Answer 1

SSMS line numbers may not match the number returned in the error always, this is because when the SQL server executes a stored proc, it has its own compilation & execution methodology, so the line numbers vary based on the execution order it takes. For example, it may some times show the line number of the previous statement but the actual error can be in the next line, few times the error line number is shown as the from clause where as the actual error might be in one of the select columns.

Most of the times, when debugging such errors, I execute the content of the stored procedure as a block with debugging enabled in the SQL server. This always helps me to verify every line in the stored proc.