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.
>
