We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
USE [CustomerIssueDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: Insert New Problem -- ============================================= ALTER PROCEDURE [dbo].[InsertNewProblem] -- Add the parameters for the stored procedure here @CustomerID int, @DateClosed DateTime = null, @DateElapsed DateTime = null, @EngVin varchar(50) = null, @HotLine bit = null, @LiveCall bit = null, @CallTypeID int = 1, @CategoryID int = 1, @Regarding varchar(max) = null, @Complaint varchar(max) = null, @Action varchar(max) = null, @Comments varchar(max) = null, @TechSupRepID int, @Suggestion bit = null, @ID int OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; 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; -- Insert statements for procedure here BEGIN TRY INSERT INTO CustomerProblem ( CustomerID , DateClosed , DateElapsed , EngVin , HotLine , LiveCall, CallTypeID , CategoryID , Regarding, Complaint, [Action] , Comments , TechSupRepID , Suggestion, Active, LastUpdated ) VALUES ( @CustomerID, @DateClosed , @DateElapsed , @EngVin , @HotLine , @LiveCall , @CallTypeID, @CategoryID , @Regarding , @Complaint , @Action , @Comments, @TechSupRepID , @Suggestion , 1, GETDATE() ); --|===================== END SQL SATEMENT =======================|-- -- 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; SET @ID = @@IDENTITY --| Returns the pkid for the output .. | --|===================== END TRY =========================|-- END TRY --|====================================================================================|-- BEGIN CATCH -- An error occurred somewhere in the try block. -- 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, who should roll back the outer transaction. --|====================================================================================|-- -- Notify the client by raising an exception with the error details. -- 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 --|====================================================================================|-- END
Join the community of 500,000 technology professionals and ask your questions.