SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[p_table_next_identity_value]
	@table_name SYSNAME,
	@next_identity_value INT OUT,
	@error_code ais_code_long OUT
AS 
	SET NOCOUNT ON;

	DECLARE @identity_field_name SYSNAME
	DECLARE @table_catalog SYSNAME
	DECLARE @table_schema sysname
	DECLARE @sql_next_identity_value NVARCHAR(2000)
	DECLARE @sql_execute_result INT 

	SELECT	@identity_field_name = c.COLUMN_NAME,
			@table_catalog = c.TABLE_CATALOG,
			@table_schema = c.TABLE_SCHEMA
	FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
		INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON		tc.TABLE_CATALOG = ccu.TABLE_CATALOG
																		AND tc.TABLE_SCHEMA = ccu.TABLE_SCHEMA
																		AND tc.TABLE_NAME = ccu.TABLE_NAME
																		AND tc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG
																		AND tc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
																		AND tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
		INNER JOIN INFORMATION_SCHEMA.COLUMNS c ON		ccu.TABLE_CATALOG = c.TABLE_CATALOG
													AND ccu.TABLE_SCHEMA = c.TABLE_SCHEMA
													AND ccu.TABLE_NAME = c.TABLE_NAME
													AND ccu.COLUMN_NAME = c.COLUMN_NAME
													AND c.DATA_TYPE IN ('int', 'smallint','bigint')
	WHERE		tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
			AND 1 = (	SELECT COUNT(*) 
						FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
						where		tc.TABLE_CATALOG = ccu.TABLE_CATALOG
								AND tc.TABLE_SCHEMA = ccu.TABLE_SCHEMA
								AND tc.TABLE_NAME = ccu.TABLE_NAME
								AND tc.CONSTRAINT_CATALOG = ccu.CONSTRAINT_CATALOG
								AND tc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
								AND tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
					)
			AND tc.TABLE_NAME = @table_name

	IF @identity_field_name IS NULL
	BEGIN
		SET @error_code = 'IDENT_KEY_NOT_FOUND'
		SET @next_identity_value = null
	END 
	ELSE
	BEGIN
		DECLARE @max_identity_value INT 

		SET @sql_next_identity_value = 'select @max_identity_value_out = max([' + @identity_field_name + ']) from [' + @table_catalog + '].[' + @table_schema + '].[' + @table_name + ']'
		--PRINT @sql_next_identity_value
		  
		EXECUTE sp_executesql	@stmt = @sql_next_identity_value,
								@params = N'@max_identity_value_out int output',
								@max_identity_value_out = @max_identity_value OUTPUT 

		IF ISNULL(@max_identity_value, 0) = 0
		BEGIN
			SELECT @max_identity_value = ao.opt_seed
			FROM ais_options ao
		END

		SET @next_identity_value = @max_identity_value + 1
		
	END
	/*
	-- execute exemple
	DECLARE @next_identity_value INT 
	DECLARE @error_code ais_code_long

	EXEC p_table_next_identity_value @table_name = 'acc_invoices', @next_identity_value = @next_identity_value out, @error_code = @error_code out

	SELECT next_identity_value = @next_identity_value, error_code = @error_code
	*/
GO
