Saturday 25 November 2017

Store Procedure for crud operation with transaction and exception handling (try catch block)

CREATE TABLE [dbo].[tblCustomer](
[CustID] [bigint] NOT NULL,
[CustName] [nvarchar](50) NULL,
[CustEmail] [nvarchar](50) NOT NULL,
[CustAddress] [nvarchar](256) NULL,
[CustContact] [nvarchar](50) NULL,
 CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(
[CustID] ASC,
[CustEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE PROCEDURE [dbo].[SP_CUSTOMER_INSERT]
(
@CustName NVarchar(50)  
    ,@CustEmail NVarchar(50)  
    ,@CustAddress NVarchar(256)  
    ,@CustContact  NVarchar(50)  
)  
AS BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DECLARE @CustID Bigint
  SET @CustID = isnull(((SELECT max(CustID) FROM [dbo].[tblCustomer])+1),'1')  
  Insert INTO tblCustomer(CustID,CustName,CustEmail,CustAddress,CustContact)Values(@CustID,@CustName,@CustEmail,@CustAddress,@CustContact)
  Select 1
  Commit Transaction
  End Try
  BEGIN CATCH
   DECLARE @ErrorMessage NVARCHAR(4000),@ErrorSeverity INT,@ErrorState INT;  
            SELECT @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();  
            RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);  
Rollback Transaction
END Catch
End
GO

CREATE  PROCEDURE [dbo].[SP_CUSTOMER_DELETE]
@CustID BIGINT  
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
DELETE tblCustomer
 WHERE [CustID] = @CustID   
        SELECT 1  
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ERROR_MEASSAGE NVARCHAR(4000),@ERRORSERVERITY INT , @ERRORLINE INT ,@ERRORSTATE INT;
SELECT @ERROR_MEASSAGE=ERROR_MESSAGE(),@ERRORSERVERITY=ERROR_SEVERITY(),@ERRORLINE=ERROR_LINE(),@ERRORSTATE=ERROR_STATE();
RAISERROR (@ERROR_MEASSAGE ,@ERRORSERVERITY,@ERRORLINE,@ERRORSTATE);
ROLLBACK TRANSACTION
END CATCH
END

CREATE PROCEDURE [dbo].[SP_CUSTOMER_UPDATE]
 @CustID BIGINT 
    ,@CustName NVarchar(50) =NULL
    ,@CustEmail NVarchar(50)  =NULL
    ,@CustAddress NVarchar(256) =NULL 
    ,@CustContact  NVarchar(50) =NULL 
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
UPDATE [dbo].[tblCustomer]
SET CustName=@CustName,
CustAddress=@CustAddress,
CustContact=@CustContact
WHERE CustID=@CustID AND [CustEmail]=@CustEmail
COMMIT TRANSACTION

END TRY

BEGIN CATCH 
SELECT  1    

DECLARE  @ErrorMessage  NVARCHAR(200),@ErrorSeverity INT ,@ErrorState int,@ErrorLinenumber INT;
select  @ErrorMessage =ERROR_MESSAGE(),@ErrorSeverity=ERROR_SEVERITY(), @ErrorState=ERROR_STATE(),@ErrorLinenumber=ERROR_LINE();
SELECT @ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLinenumber
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState,@ErrorLinenumber)
ROLLBACK TRANSACTION
END CATCH
END

CREATE PROCEDURE [dbo].[SP_SELECT_CUSTOMER_BY_ID]
@CustID  BIGINT
AS BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
SELECT * FROM [dbo].[tblCustomer]
WHERE CustID=@CustID
SELECT 1
COMMIT TRANSACTION
END TRY
BEGIN CATCH 
DECLARE @ERRORMESAGE NVARCHAR(400),@ERRORSERVERITY INT,@ERRORSTATUS INT, @ERRORLINE INT,@ERRORPROCEDURE NVARCHAR(50);
SELECT @ERRORMESAGE=ERROR_MESSAGE(),@ERRORsERVERITY=ERROR_SEVERITY(),@ERRORSTATUS=ERROR_STATE(),@ERRORLINE=ERROR_LINE(),
@ERRORPROCEDURE=ERROR_PROCEDURE();
RAISERROR (@ERRORMESAGE,@ERRORsERVERITY,@ERRORSTATUS,@ERRORLINE,@ERRORPROCEDURE)
ROLLBACK TRANSACTION
END CATCH
END
---------------------------------------------------------------------
Note: -Default exception in SQL Server 2014 is

ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;

The severity parameter specifies the severity of the exception.

How to get logged in User's Security Roles using Java Script in dynamic CRM 365.

 function GetloggedUser () {     var roles = Xrm.Utility.getGlobalContext().userSettings.roles;      if (roles === null) return false;      ...