How do you write a recursive stored procedure
- by Grayson Mitchell
I simply want a stored procedure that calculates a unique id 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.
ALTER PROCEDURE [dbo].[DataContainer_Insert]
@SomeData varchar(max),
@DataContainerId int out
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
SELECT @UserId = MAX(UserId) From DataContainer
INSERT INTO DataContainer (UserId, SomeData)
VALUES (@UserId, SomeData)
SELECT @DataContainerId = scope_identity()
END TRY
BEGIN CATCH
--try again
exec DataContainer_Insert @DataContainerId, @SomeData
END CATCH
END
