>Keith, I´m agree with you and I did a second version with a special table that manages only the ID's incremental values for each table.
>
>But I have some doubts about this matter.
>- How can I get the ophraned connections ?
>- How can I do a plan to clean up this connections ?
>- How can I do to avoid a database freezing ?
>
>T.I.A.
>
>

>SET ANSI_NULLS ON
>SET QUOTED_IDENTIFIER ON
>go
>
>ALTER PROCEDURE [dbo].[Nextcode]
>        @myColumn varchar(50)
>AS
>
>SET NOCOUNT ON
>
>DECLARE
>    @return_val int,
>    @sqlcmd nvarchar(100)
>
>-- myIdentityTable is a special table in D.B. that contains
>--  only a record and so many columns as tables in the D.B.
>-- be necessary to give an ID.
>-- e.g.: myIdentityTable.invoice, myIdentityTable.products, etc.
>
>
>BEGIN TRAN t1
>SET @sqlcmd = 'update myIdentiyTable + ' set ' + @myColumn + ' = ' + @myColumn + ' + 1'
>EXEC sp_executesql @sqlcmd
>SET @sqlcmd = 'select ' + @myColumn + ' as myID from myIdentityTable
>EXEC sp_executesql @sqlcmd, N'@return_val int output', @return_val output
>if @@ERROR > 0
>	BEGIN
>		ROLLBACK TRAN t1
>		SET @return_val = -1
>		RETURN @return_val
>	END
>ELSE
>	BEGIN
>		COMMIT TRAN t1
>		RETURN @return_val
>	END
>

>
>
>>Ricardo,
>>
>>I'm sorry, I wasn't clear. What I meant was that in order to produce your own incremental values, you have to use a separate table that stores the current (last used) value and make sure that the transactions are structured correctly. You will also need to augment the separate table with a maintenance plan that cleans up locks from an orphaned connection.
>>
>>Even with each of these parts, the whole thing can get fouled up fairly easily. This can mean that the generated numbers are way off (maybe this is a problem for you, maybe it isn't) - and it can also completely freeze up the database if an orphaned transaction holds an update lock on the autonumber row/page/table.
>>
>>IOW, avoid this if at all possible.
>>

This is more or less our solution:
Table:
ID int PK/Ident
TableName nvarchar(200)
LastIdentity int

Pseudo Code:
Params: TableName (in), Ident(Out)

If (select count(*) from tableident where tablename=@tablename)=1 then
begin trans
table lock in high use
select @ident=lastidentity+1 from tableident where tablename=@tablename
update tableident set lastidentity=@ident where tablename=@tablename
commit
release table
end trans
return @ident

Missing error traps, but this is the 100000 feet view. The other alternative would be to wrap it into an UDF