How do you write a recursive stored procedure

Question

I simply want a stored procedure that calculates a unique id (that is separate from the identity column) and inserts it. If it fails it just calls itself to regenerate said id. I have been looking for an example, but cant find one, and am not sure how I should get the SP to call itself, and set the appropriate output parameter. I would also appreciate someone pointing out how to test this SP also.

Edit

What I have now come up with is the following (Note I already have an identity column, I need a secondary id column.

ALTER PROCEDURE [dbo].[DataInstance_Insert] 
    @DataContainerId int out,
    @ModelEntityId int,
    @ParentDataContainerId int, 
    @DataInstanceId int out
AS
BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    WHILE (@DataContainerId is null) 
        EXEC DataContainer_Insert @ModelEntityId, @ParentDataContainerId, @DataContainerId output   

    INSERT INTO DataInstance (DataContainerId, ModelEntityId)
    VALUES (@DataContainerId, @ModelEntityId)   

    SELECT @DataInstanceId = scope_identity()                       
END    

    ALTER PROCEDURE [dbo].[DataContainer_Insert]
        @ModelEntityId int,
        @ParentDataContainerId int,
        @DataContainerId int out
    AS
    BEGIN 
    BEGIN TRY
        SET NOCOUNT ON;
        DECLARE @ReferenceId int

        SELECT @ReferenceId = isnull(Max(ReferenceId)+1,1) from DataContainer Where ModelEntityId=@ModelEntityId

        INSERT INTO DataContainer (ReferenceId, ModelEntityId, ParentDataContainerId)
        VALUES (@ReferenceId, @ModelEntityId, @ParentDataContainerId)

        SELECT @DataContainerId = scope_identity()
    END TRY
    BEGIN CATCH
    END CATCH
    END

Answer 1

Why not use the:

 NewId() 

T SQL function? (assuming sql server 2005/2008)

Answer 2

that sp will never ever do a successful insert, you have an identity property on the DataContainer table but you are inserting the ID, in that case you will need to set identity_insert on but then scope_identity() won't work

A PK violation also might not be trapped so you might also need to check for XACT_STATE()

why are you messing around with max, use scope_identity() and be done with it

Answer 3

Better yet, why not make UserID an identity column instead of trying to re-implement an identity column manually?

BTW: I think you meant

VALUES (@DataContainerId + 1 , SomeData)

Answer 4

1. In CATCH blocks you must check the XACT_STATE value. You may be in a doomed transaction (-1) and in that case you are forced to rollback. Or your transaction may had already had rolled back and you should not continue to work under the assumption of an existing transaction. For a template procedure that handles T-SQL exceptions, try/catch blcoks and transactions correctly, see Exception handling and nested transactions
2. Never, under any languages, do recursive calls in exception blocks. You don't check why you hit an exception, therefore you don't know if is OK to try again. What if the exception is 652, read-only filegroup? Or your database is at max size? You'll re-curse until you'll hit stackoverflow...
3. Code that reads a value, makes a decision based on that value, then writes something is always going to fail under concurrency unless properly protected. You need to wrap the SELECT and INSERT in a transaction and your SELECT must be under SERIALISABLE isolation level.

And finally, ignoring the blatantly wrong code in your post, here is how you call a stored procedure passing in OUTPUT arguments:

exec DataContainer_Insert  @SomeData, @DataContainerId OUTPUT;