SQL 服务器 : Alternative for using Exec in Functions

Question

我正在尝试创建一个函数，我可以调用它来检查哪个是我数据库中每个表的键中的下一个 ID。我想出了如何去做，但我无法创建该函数，因为我收到此错误:

“在函数中无效使用副作用运算符‘INSERT EXEC’。”

看来我不能在函数中使用 Exec。我必须通过哪些替代方法才能获取此信息？

这是我的代码:

CREATE FUNCTION FCN_ProximoID()
RETURNS @TablaID Table (Tabla nvarchar(370), ID int)
AS
BEGIN
-- Fill the table variable with the rows for your result set
DECLARE @Row INT
DECLARE @Filas INT
DECLARE @MaxID INT
DECLARE @Query As varchar(max)
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)



SET @Filas = (SELECT MAX(Fila) 
    FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY A.TABLE_NAME) AS 'Fila', 
                          A.TABLE_NAME As Tabla, A.COLUMN_NAME As Columna, 
        A.ORDINAL_POSITION As Indice, B.DATA_TYPE As TipoDato
        FROM 
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS B
        ON A.COLUMN_NAME = B.COLUMN_NAME AND A.TABLE_NAME=B.TABLE_NAME
        WHERE LEFT(A.TABLE_NAME,3)='EXT' AND OBJECTPROPERTY(OBJECT_ID
                          (constraint_name), 'IsPrimaryKey')=1 AND B.DATA_TYPE='int'
        ) As Tablas)

SET @Row = 1

WHILE (@Row <= @Filas)
    BEGIN
    BEGIN

    SET @TableName = (SELECT Tabla
        FROM (
            SELECT ROW_NUMBER() OVER(ORDER BY A.TABLE_NAME) AS 'Fila', 
                                    A.TABLE_NAME As Tabla, A.COLUMN_NAME As Columna, 
                                    A.ORDINAL_POSITION As Indice, B.DATA_TYPE As TipoDato
            FROM 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
            LEFT JOIN INFORMATION_SCHEMA.COLUMNS B
            ON A.COLUMN_NAME = B.COLUMN_NAME AND 
                                    A.TABLE_NAME=B.TABLE_NAME
            WHERE LEFT(A.TABLE_NAME,3)='EXT' AND OBJECTPROPERTY(OBJECT_ID
                                     (constraint_name), 'IsPrimaryKey')=1 AND B.DATA_TYPE='int'
            ) As Tablas WHERE Fila=@Row) 

    SET @ColumnName = (SELECT Columna
        FROM (
            SELECT ROW_NUMBER() OVER(ORDER BY A.TABLE_NAME) AS 'Fila',
                                    A.TABLE_NAME As Tabla, A.COLUMN_NAME As Columna, 
            A.ORDINAL_POSITION As Indice, B.DATA_TYPE As TipoDato
            FROM 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
                LEFT JOIN INFORMATION_SCHEMA.COLUMNS B
                ON A.COLUMN_NAME = B.COLUMN_NAME AND 
                                               A.TABLE_NAME=B.TABLE_NAME
                WHERE LEFT(A.TABLE_NAME,3)='EXT' AND 
                                              OBJECTPROPERTY(OBJECT_ID
                                              (constraint_name), 'IsPrimaryKey')=1 
                                              AND B.DATA_TYPE='int'
            ) As Tablas WHERE Fila=@Row) 


INSERT INTO @TablaID

EXEC('SELECT ''' + @TableName + ''', ISNULL(MAX(' + @ColumnName + '),0)+1 FROM ' + @TableName )



SET @Row = @Row + 1


END
END 


RETURN 
END
GO

Answer 1

Exec 不允许在函数中使用，但可以在存储过程中使用，因此您只需将函数重写为存储过程即可返回结果集。