时间戳

首页 > 解决方案 > 存储过程工作正常，但突然停止某些值

sql - 存储过程工作正常，但突然停止某些值

问题描述

因此，我一直在为我正在构建的 SSRS 报告处理一些存储过程，并且遇到了一个奇怪的错误，需要一双新的眼睛来看看我可能遗漏了什么。

我的程序非常简单 -SELECT一些JOINed 表中的各种列，INSERT它们进入一个#temp表，表SELECT的所有内容在我的报告中显示为详细行。

我的完整过程如下所示：

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

正如我所说，我一直对这段代码有一个奇怪的问题。在过去的两周里，所有测试用例都运行良好。我EXEC用来根据参数选择记录@PR_ID，它工作正常。昨天，在没有触及任何代码之后，我开始为某些PR_ID值生成错误代码：

消息 8114，级别 16，状态 5，过程 rpt_select_ACHW_Ob，第 28 行 [Batch Start Line 2]
将数据类型 varchar 转换为数字时出错。

第 28 行将您引向FC VARCHAR(50)我已经检查了 10 次的地方。表中声明的所有数据类型都#temp与选择的值完全匹配。有没有人知道为什么这已经停止工作？

这是一个带有一些示例数据的 dbFiddle 链接

目前正在使用 SQL Server 2012。

标签： sqlsql-server

解决方案

发生了两件事之一。您的原始查询编写正确并且有人更改了基础表（将整数列更改为字符串列）然后用非数字数据填充字符串列的可能性较小。

更有可能的情况是您的原始查询具有隐式转换。这只是一个等待发生的问题——现在你知道为什么了。您收到来自 SQL Server 的错误消息，它没有指定出现问题的表、行或列。啊啊啊！

我的建议是通过查询并检查每个表达式和比较，以确保类型兼容（数字/数字、字符串/字符串、日期时间/日期时间就足够了）。如果不是，请添加显式转换。try_convert()您可以使用or添加转换try_cast()，这至少可以避免错误（以产生NULLs 为代价）。

我希望 SQL Server 有一个“无隐式转换”模式，它会警告您查询正在使用此类转换。唉，没有。因此，养成编写查询的习惯，以便所有转换都是明确的。

编辑：

例如（基于评论），这个表达式：

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

应该：

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

请注意，您应该在 SQL Server中的所有CHAR()/引用中包含长度。VARCHAR()

推荐阅读