存储过程中数值参数使用及WHILE循环转换错误求助

看起来你在把正常运行的SQL逻辑改成存储过程时，踩了动态SQL变量拼接的坑。核心问题出在你直接把数值/日期类型的变量硬拼到字符串里，导致语法错误和类型转换异常，同时对动态SQL的变量作用域理解有误。

先拆解下你遇到的具体问题：

• 直接将整数@months、日期@StartDate拼入动态SQL字符串，SQL Server无法正确解析这些非字符串类型的变量，触发转换错误
• 拼接时写错了DATEADD函数的括号，导致语法报错
• 试图在动态SQL中直接操作存储过程的外部变量，这是不符合SQL Server变量作用域规则的

下面给你两种解决方案，优先推荐第二种，更简洁易维护：

方案1：修正动态SQL拼接（不推荐，仅作参考） #

如果一定要用动态SQL，需要把循环逻辑完全放到动态SQL内部，同时正确处理变量和语法：

CREATE PROCEDURE GetScaffStandingByTime
    @Account VARCHAR(100) = 'BradyTechUK'
AS
BEGIN
    SET NOCOUNT ON;

    -- 创建临时表存储结果
    CREATE TABLE #tempScaffStandingByTime ( TotalStanding INT, MonthsAgo INTEGER )

    -- 构建切换数据库的语句，用QUOTENAME避免特殊字符报错
    DECLARE @UseDB NVARCHAR(200) = N'USE [Safetrak-' + QUOTENAME(@Account, '''') + N']';

    -- 动态SQL主体，把循环逻辑放在内部，避免外部变量作用域问题
    DECLARE @DynamicSQL NVARCHAR(MAX) = 
    @UseDB + N'
    DECLARE @StartDate DATETIME;
    DECLARE @months INTEGER = 12;
    WHILE @months >= 0
    BEGIN
        SET @StartDate = DATEADD(mm, -12 + @months, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm,0,GETDATE()-1), 1)));
        INSERT INTO #tempScaffStandingByTime
        SELECT TOP 1 COUNT(*) OVER () AS TotalRecords, @months
        FROM asset a
        JOIN wshhistory h ON h.assetid = a.uid
        WHERE a.deleted = 0 AND h.assetstate <> 6 AND (dbo.getdecommissiondate(a.uid) > @StartDate)
        GROUP BY a.uid;
        SET @months -= 3;
    END';

    -- 执行动态SQL
    EXEC sp_executesql @DynamicSQL;

    -- 返回最终结果
    SELECT TotalStanding, MonthsAgo FROM #tempScaffStandingByTime;

    -- 清理临时表
    DROP TABLE #tempScaffStandingByTime;
END

方案2：避免动态SQL（强烈推荐） #

其实你根本不需要把整个循环放进动态SQL，只需要先切换目标数据库，再执行你原本就能正常运行的静态逻辑即可，完全规避动态SQL的坑：

CREATE PROCEDURE GetScaffStandingByTime
    @Account VARCHAR(100) = 'BradyTechUK'
AS
BEGIN
    SET NOCOUNT ON;

    -- 先切换到目标数据库
    DECLARE @UseDB NVARCHAR(200) = N'USE [Safetrak-' + QUOTENAME(@Account, '''') + N']';
    EXEC sp_executesql @UseDB;

    -- 创建临时表
    CREATE TABLE #tempScaffStandingByTime ( TotalStanding INT, MonthsAgo INTEGER )

    -- 直接复用你原本能正常运行的循环逻辑，此时已经在目标数据库上下文里了
    DECLARE @StartDate DATETIME = NULL;
    DECLARE @months INTEGER = 12;

    WHILE @months >= 0
    BEGIN
        SET @StartDate = DATEADD(mm, -12 + @months, DATEADD(mm, 0, DATEADD(mm, DATEDIFF(mm,0,GETDATE()-1), 1)));
        INSERT INTO #tempScaffStandingByTime
        SELECT TOP 1 COUNT(*) OVER () AS TotalRecords, @months
        FROM asset a
        JOIN wshhistory h ON h.assetid = a.uid
        WHERE a.deleted = 0 AND h.assetstate <> 6 AND (dbo.getdecommissiondate(a.uid) > @StartDate)
        GROUP BY a.uid;
        SET @months -= 3;
    END

    -- 返回结果
    SELECT TotalStanding, MonthsAgo FROM #tempScaffStandingByTime;

    -- 清理临时表
    DROP TABLE #tempScaffStandingByTime;
END

关键注意点 #

• QUOTENAME函数用来处理@Account中可能存在的特殊字符，避免数据库名称拼接时的语法错误，同时防止SQL注入风险
• SET NOCOUNT ON可以避免存储过程返回额外的“影响行数”信息，对SSRS报表更友好
• 方案2的逻辑和你原本的可运行查询完全一致，调试和维护成本低，是最优选择

内容的提问来源于stack exchange，提问作者GertDeWilde