Julio César Carrascal Urquijo challenges us to spot the number of ... irregularities ... in this ... interesting ... implementation of a sequential id (identifier) generator in T-SQL. My only guess is that the developer here came from the elite world of Oracle, where even the simplest things like sequences are painfully difficult.

ALTER PROCEDURE pa_actab001_id_001 (
 @table_name VARCHAR(255)= NULL,
 @id INT = NULL OUTPUT /* return variable */
) AS
/*********************************************************
This procedure searches an unexistent id (identifier) and 
valid for a table. It's necesary that the identifier be of 
type integer and has the same name as the table +id. 
A temporary table #temp it's used to store the value, 
then it is destroyed
**********************************************************/
  CREATE TABLE #temp (id int )
  INSERT #temp execute ('
    SELECT ID = 
      CASE 
        WHEN MIN('+@table_name+'_id)+1 IS NULL THEN 1
        ELSE MIN('+@table_name+'_id)+1 
      END  
    FROM '+@table_name+'
    WHERE '+@table_name+'_id >= 1 
      and '+@table_name+'_id < (2147483647 - 1) 
      and '+@table_name+'_id+1 not in 
        (SELECT '+@table_name+'_id FROM '+@table_name+')' )
  set @id =( select * from #temp)
  DROP TABLE #temp
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!