SP SQL

 -- =============================================

-- Author : Praveen

-- Create Date :

-- Description : USP_UpdateCountry

-- =============================================

-- Exec [USP_UpdateUser]

CREATE PROCEDURE [dbo].[USP_UpdateUser]

@dtUser [dbo].[UDTT_Users] READONLY, 

@dtUserEntities [dbo].[UDTT_UserAccessEntities] READONLY

AS

BEGIN

BEGIN TRANSACTION [UpdateDetails]

BEGIN TRY

COMMIT TRANSACTION [UpdateDetails]

END TRY    

    BEGIN CATCH

ROLLBACK TRANSACTION [UpdateDetails]

SELECT '0' AS ID, 'false' AS [Status], 'Failed to save' as [Message]

INSERT INTO tbl_Errors(ErrorNumber,ErrorState,ErrorSeverity,ErrorLine,ErrorProcedure,ErrorMessage,ErrorDateTime )

SELECT  

ERROR_NUMBER() AS ErrorNumber, ERROR_STATE() AS ErrorState, ERROR_SEVERITY() AS ErrorSeverity 

,ERROR_LINE() AS ErrorLine, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_MESSAGE() AS ErrorMessage

,GETDATE()

    END CATCH

END

------------------table--------

CREATE TABLE [dbo].[tbl_Errors](

[ErrorID] [int] IDENTITY(1,1) NOT NULL,

[UserName] [nvarchar](100) NULL,

[ErrorNumber] [int] NULL,

[ErrorState] [int] NULL,

[ErrorSeverity] [int] NULL,

[ErrorLine] [int] NULL,

[ErrorProcedure] [nvarchar](max) NULL,

[ErrorMessage] [nvarchar](max) NULL,

[ErrorDateTime] [datetime] NULL,

PRIMARY KEY CLUSTERED 

(

[ErrorID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO