You could use one of stored procedure templates / boilerplate provided by Microsoft: here(example B or C; for non-nested transactions) or here(for nested transactions). Also, you could use RAISERROR to generate an exception if some conditions aren't meet.
Example (based on the last template - [nested transactions]):
IF EXISTS (SELECT name FROM sys.objects WHERE name = N'dbo.Country_Insert') DROP PROCEDURE dbo.Country_Insert; GO CREATE PROCEDURE dbo.Country_Insert ( @p_countryName nvarchar(128) ,@ret_countryId int OUTPUT ) AS -- Detect whether the procedure was called -- from an active transaction and save -- that for later use. -- In the procedure, @TranCounter = 0 -- means there was no active transaction -- and the procedure started one. -- @TranCounter > 0 means an active -- transaction was started before the -- procedure was called. DECLARE @TranCounter INT; SET @TranCounter = @@TRANCOUNT; IF @TranCounter > 0 -- Procedure called when there is -- an active transaction. -- Create a savepoint to be able -- to roll back only the work done -- in the procedure if there is an -- error. SAVE TRANSACTION ProcedureSave; ELSE -- Procedure must start its own -- transaction. BEGIN TRANSACTION; -- Modify database. BEGIN TRY -- Custom source code IF len(@p_countryName) <= 3 BEGIN SET @ret_countryId = null RAISERROR('Wrong contry name', 16, 1); END INSERT INTO [Dictionaries].[CountryDetails] (name) VALUES (@p_countryName); SET @ret_countryId = SCOPE_IDENTITY() -- @@IDENTITY isn't safe INSERT INTO [Dictionaries].[Places] (name, Discriminator , CountryDetails_Id, RegionDetails_Id, CityDetails_Id) VALUES (@p_countryName, 'Country' , @ret_countryId, null, null); -- End of Custom source code -- Get here if no errors; must commit -- any transaction started in the -- procedure, but not commit a transaction -- started before the transaction was called. IF @TranCounter = 0 -- @TranCounter = 0 means no transaction was -- started before the procedure was called. -- The procedure must commit the transaction -- it started. COMMIT TRANSACTION; END TRY BEGIN CATCH -- An error occurred; must determine -- which type of rollback will roll -- back only the work done in the -- procedure. IF @TranCounter = 0 -- Transaction started in procedure. -- Roll back complete transaction. ROLLBACK TRANSACTION; ELSE -- Transaction started before procedure -- called, do not roll back modifications -- made before the procedure was called. IF XACT_STATE() <> -1 -- If the transaction is still valid, just -- roll back to the savepoint set at the -- start of the stored procedure. ROLLBACK TRANSACTION ProcedureSave; -- If the transaction is uncommitable, a -- rollback to the savepoint is not allowed -- because the savepoint rollback writes to -- the log. Just return to the caller, which -- should roll back the outer transaction. -- After the appropriate rollback, echo error -- information to the caller. DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(); SELECT @ErrorSeverity = ERROR_SEVERITY(); SELECT @ErrorState = ERROR_STATE(); RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH GO
Note: As you can see, I've used SCOPE_IDENTITY() instead of @@IDENTITY. The reason is explained here.
