将nvarchar转换为int的动态SQL错误

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

我在动态SQL中创建了一个具有select语句的过程，代码如下所示：

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

I am getting the following error.

我收到以下错误。

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

Msg 245，Level 16，State 1，Procedurecagroup，Line 33将nvarchar值'SELECT [Gro] AS GName，'转换为数据类型int时转换失败。

Where am I doing wrong?

我哪里做错了？

2 个解决方案

#1

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

您需要在串联中将所有数字CAST到nvarchar。

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.

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

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

Edit: Will A has a good point: watch for NULLs!

编辑：A有一个好点：注意NULL！

#2

If you have to build this kind of dynamic SQL, it is better to get the column information from the meta-data than to pass it around.

如果必须构建这种动态SQL，最好从元数据中获取列信息，而不是传递它。

Select * from Information_Schema.Columns Where Table_name=@TableName

The you have to write an ugly cursor to build the SQL. Expect performance problems. I do lots of this during development to write code for me, but I don't dare run it in production.

你必须编写一个丑陋的游标来构建SQL。期待性能问题。我在开发过程中为我编写代码做了很多，但我不敢在生产中运行它。

#1