动态SQL错误将nvarchar转换为int [英] Dynamic SQL error converting nvarchar to int

问题描述

我已经在动态SQL中创建了一个过程，该过程具有一个select语句，并且代码如下：

I have created a procedure in dynamic SQL which has a select statement and the code looks like:

ALTER PROCEDURE cagroup    (
    @DataID INT ,
    @days INT ,
    @GName VARCHAR(50) ,
    @T_ID INT ,
    @Act BIT ,
    @Key VARBINARY(16)
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @SchemaName SYSNAME
    DECLARE @TableName SYSNAME
    DECLARE @DatabaseName SYSNAME
    DECLARE @BR CHAR(2)
    SET @BR = CHAR(13) + CHAR(10)

    SELECT  @SchemaName = Source_Schema ,
            @TableName = Source_Table ,
            @DatabaseName = Source_Database
    FROM    Source
    WHERE   ID = @DataID

SET @SQL =  'SELECT ' + @GName + ' AS GrName ,' + @BR
                + @T_ID + ' AS To_ID ,' + @BR
                + @DataID + ' AS DataSoID ,' + @BR
                + @Act + ' AS Active ,' + @BR
                + Key + ' AS key' + @BR
                + 'R_ID AS S_R_ID' + @BR
                + 'FROM' + @DatabaseName + '.'
                + @SchemaName + '.'
                + @TableName + ' t' + @BR
                + 'LEFT OUTER JOIN Gro g ON g.GName = '
                    + @GName + @BR + 'AND g.Data_ID] =' + @DataID + @BR
                    + 't.[I_DATE] > GETDATE() -' + @days + @BR
                    + 'g.GName IS NULL
                        AND ' + @GName + ' IS NOT NULL
                        AND t.[Act] = 1' + @BR

    PRINT (@SQL)
END

何时我正在使用以下语句执行此过程：

When I am executing this procedure with this statement:

Exec  dbo.cagroup  1,10,'[Gro]',1,1,NULL

我遇到以下错误。

消息245，级别16，状态1，procedurecagroup，第33行
将nvarchar值'SELECT [Gro] AS GName，
'转换为数据类型时，转换失败

Msg 245, Level 16, State 1, Procedurecagroup, Line 33 Conversion failed when converting the nvarchar value 'SELECT [Gro] AS GName , ' to data type int.

我在哪里做错了？

推荐答案

需要在串联中将所有数字都转换为nvarchar。

You need to CAST all numbers to nvarchar in the concatenation.

没有隐式的VBA样式转换为字符串。在SQL Server中，数据类型优先级意味着int高于nvarchar：因此整个字符串都试图通过CAST转换为int。

There is no implicit VBA style conversion to string. In SQL Server data type precedence means ints are higher then nvarchar: so the whole string is trying to be CAST to int.

SET @SQL =  'SELECT ' + @GName + ' AS GrName ,' + @BR
              + CAST(@T_ID AS nvarchar(10)) + ' AS To_ID ,' ...

编辑：A会说点好吗？请注意NULL！

Will A has a good point: watch for NULLs!

这篇关于动态SQL错误将nvarchar转换为int的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！