主页 / dba / 问题 / 27847
Accepted
Marin
Asked: 2012-10-30 14:18:27 +0800 CST

将 varchar 转换为 int 失败

  • 772

运行此代码时：

declare @SQL NVARCHAR(max);
declare @intFlag INT;
set @intFlag = 2;

SET @SQL = ' SELECT * into [##tmp1] 
FROM servicesstats_0511_0412 s WHERE department=''ACC''
and ((SELECT COUNT(*) FROM servicesstats_0511_0412 WHERE department <> ''ACC''
and s.studentid=studentid ) = ' + @intFlag + ')';

EXEC SP_EXECUTESQL @SQL

我收到以下错误：

将 varchar 值 ' SELECT * 转换为 [##tmp1] FROM servicesstats_0511_0412 s WHERE department='ACC' and ((SELECT COUNT(*) FROM servicesstats_0511_0412 WHERE department <> 'ACC' and s.studentid=studentid ) 时转换失败' 到数据类型 int。

servicesstats_0511_0412 将是动态的，因此需要动态 SQL。

我认为这不应该发生，因为 count(*) 总是返回一个 Int。即使将子查询转换为 Int 也不起作用。有什么我想念的吗？谢谢。

sql-server-2008

3 个回答

  1. Best Answer

    int具有更高的数据类型优先级，[n]varchar因此您需要在字符串连接中int显式转换为[n]varchar，以避免字符串被隐式转换为整数（由于字符串不是数字而失败）

    SET @SQL = N'
SELECT * into [##tmp1] 
FROM servicesstats_0511_0412 s 
WHERE department=''ACC''
AND ((SELECT COUNT(*) 
      FROM servicesstats_0511_0412 
      WHERE department <> ''ACC'' and s.studentid=studentid )
         = ' + CAST(@intFlag AS NVARCHAR(11)) + N')';

    或者根本不连接它并将其作为参数传递给sp_executesql。

    SET @SQL = N'
SELECT * into [##tmp1] 
FROM servicesstats_0511_0412 s 
WHERE department=''ACC''
AND ((SELECT COUNT(*) 
      FROM servicesstats_0511_0412 
      WHERE department <> ''ACC'' and s.studentid=studentid ) = @intFlag)';

EXEC sp_executesql @SQL, N'@intFlag INT', @intFlag = @intFlag
    • 8

  2. 虽然 Martin 已经完全回答了您的问题，但我完全不明白您为什么需要在这里使用动态 SQL。

    declare @intFlag INT;
set @intFlag = 2;

SELECT *
into [##tmp1]
FROM servicesstats_0511_0412 s
WHERE department='ACC'
  and (SELECT COUNT(*)
      FROM servicesstats_0511_0412
      WHERE department <> 'ACC' and s.studentid=studentid ) = @intFlag;

    也许你只见树木不见森林？

    • 2

  3. 问题不在这里 count(*)

    declare @intFlag INT;  change -to    declare @intFlag varchar(20)='2'

    或者

    SET @SQL = ' SELECT * into [##tmp1] 
FROM servicesstats_0511_0412 s WHERE department=''ACC''
and ((SELECT COUNT(*) FROM servicesstats_0511_0412 WHERE department <> ''ACC'' and s.studentid=studentid ) = ' +CONVERT(varchar(100),@intFlag) + ')';
    • 0

相关问题