SQL Server: Alternative for using Exec in Functions

Question

I am trying to create a Function that I could call to check which is the next ID in key of each table in my database. I figure out how to do it, but I can not create the function because I get this error:

"Invalid use of a side-effecting operator 'INSERT EXEC' within a function."

It seems that I can not use Exec in Functions. Which alternatives could I have to get this information?

This is my code:

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 is not allowed in functions, but it is allowed in stored procedures, so you can just rewrite the function as a stored procedure which retuns a resultset.