DROP PROCEDURE sopGetMasterNumber
GO
CREATE PROCEDURE sopGetMasterNumber
@O_iOUTMasterNumber int = NULL OUTPUT,
@O_iErrorState int = NULL OUTPUT
AS
DECLARE @tTransaction tinyint,
@iError int
SELECT
@O_iOUTMasterNumber = 0,
@O_iErrorState = 0
BEGIN TRY
BEGIN TRAN
-- be carefull not to exit this or the transaction will not get committed, you'll lock the table up
UPDATE SOP40100 SET
@O_iOUTMasterNumber = NXTMSTNO,
NXTMSTNO = NXTMSTNO + 1
--declare @a int = 0
--declare @b int
--set @b = 5 / @a
COMMIT TRAN
END TRY
BEGIN CATCH
--find out what went wrong
--SELECT
-- ERROR_NUMBER() AS ErrorNumber,
-- ERROR_SEVERITY() AS ErrorSeverity,
-- ERROR_STATE() AS ErrorState,
-- ERROR_PROCEDURE() AS ErrorProcedure,
-- ERROR_LINE() AS ErrorLine,
-- ERROR_MESSAGE() AS ErrorMessage;
--roll everything back
ROLLBACK TRAN
select @O_iOUTMasterNumber = 0, @O_iErrorState = 21035
END CATCH
RETURN
go
grant exec on sopGetMasterNumber to dyngrp