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!