/* ================================================
 -- ******** LLAMADA
 
    DECLARE @ID NUMERIC
     
    --SELECT * FROM tbCargas ORDER BY IDCarga DESC
     
    EXEC @ID = dbo.ID_NUEVO 'CARTERA.dbo.tbClientes','IdCliente'
     
    PRINT @ID
   
   ================================================ */
     
    -- DROP PROCEDURE ID_NUEVO
    CREATE PROCEDURE [dbo].[ID_NUEVO]
                --***>> PARÁMETROS
                @TABLA CHAR(100),
                @COL_ID CHAR(20)
               
    --RETURNS NUMERIC
   
   
    AS
    BEGIN
   
    --DECLARE @TABLA VARCHAR(50)
    --SELECT @TABLA = 'tbCargas'
           
    DECLARE @SQL NCHAR(255)
    DECLARE @MAX_ID NUMERIC
           
    --SELECT COL_ID = COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = @TABLA
     
    SELECT @SQL = 'SELECT @MAX_ID = MAX(' + @COL_ID + ') FROM ' + @TABLA
    EXECUTE sp_executesql @SQL ,N'@MAX_ID varchar(30) OUTPUT', @MAX_ID=@MAX_ID OUTPUT
     
	IF @MAX_ID IS NULL
	BEGIN
		SELECT @MAX_ID = 0
	END
	ELSE
	BEGIN
		 SELECT @MAX_ID =@MAX_ID + 1	 
	END		
   
    RETURN @MAX_ID
     
    --PRINT 'TABLA : ' + @Tabla
    --PRINT 'COLUMNA ID : ' + @@COL_ID
    --PRINT 'SQL : ' + @SQL
     
    --PRINT 'MAX ID : '  + CONVERT(VARCHAR,@MAX_ID)
           
END

GO