Cursor In SQL SERVER

Cursor:- Cursor is  a database object to retrieve data from a result set one row at time. we use cursor when we need to update record databse table in Sigleton fashion means row wise.



  For Exampl :
   select * from Products

 know i want change  unit price of unit of each based on some condition.

Declare @UnitPrice decimal(5,2)
Declare @ProductId int
Declare UnitPriceUpdateCursore  Cursor FOR
Select ProductID From Products
open  UnitPriceUpdateCursore
Fetch Next From UnitPriceUpdateCursore into @ProductId
While (@@Fetch_Status=0)
Begin
select @UnitPrice=UnitPrice from Products

Begin Update Products set UnitPrice = case when UnitPrice between 5 And 10 then 15
                     when UnitPrice  between 11 AND 15  then 20
     else UnitPrice
                    end
Where ProductID=@ProductId
Fetch Next From UnitPriceUpdateCursore into @ProductId
END
End
Close UnitPriceUpdateCursore
Deallocate UnitPriceUpdateCursore
Set NoCount Off

MVC Validation

Expressions for input fields

Alphabets and Space

[a-zA-Z ]+$

Alphabets

^[A-z]+$

Numbers

^[0-9]+$

Alphanumeric

^[a-zA-Z0-9]*$

Email

[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?

Mobile no.

^([7-9]{1})([0-9]{9})$

Date Format( mm/dd/yyyy | mm-dd-yyyy | mm.dd.yyyy)

/^(0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])[- /.](19|20)\\d\\d+$/

Website URL

^http(s)?://([\\w-]+.)+[\\w-]+(/[\\w- ./?%&=])?$

Credit Card Numbers

Visa

^4[0-9]{12}(?:[0-9]{3})?$

MasterCard

^5[1-5][0-9]{14}$

American Express

^3[47][0-9]{13}$

Decimal number

((\\d+)((\\.\\d{1,2})?))$

Login page using AS.NET MVC C#.

How to bind data in Drop down List from Enum usig Array in Asp.net C#

Add New Class and rename as CommonEnum.

Note:- Set location in App_Code.So it can access any in a project. 

 Add method 

nameSpace Enum

{

Public ClassCommonEnum()

{

}

Public enum Operation 

{Insert =1,

Update=2,

Delete=3,

Select=4

}

}

Add New  Web Page 

 Add Asp .Net DropDownList control in .aspx page

<asp:DropDownList Id="ddlOperation"  runat= "Server"/>

Add name Space on web Page.cs

 Array arrop=Enum.GetValues(typeof(CommonEmum.Operation));

foreach(CommonEnum.Operation opt  in arrop)

{

ddlOperation.Items.Add(new ListItem(opt.ToString(),((Int)opt).Tostring()));

} 

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.

Paging store Procedure to increase the performance data loading

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

Splite Function use4

declare @receivedsetupPeriod varchar(1000),@currentperiod varchar(1000),@toperiod varchar(1000),@fromperiod varchar(1000)
select @receivedsetupPeriod = tbl_ReceivalbeSetupPeriodId,@currentperiod = CurrentPeriod,@toperiod = [TO],@fromperiod= [FROM] from tbl_ReceivableSetup

select R.Id,R.Item as receivedid,C.Item as Currentperiod,T.Item as [To],F.Item as [From] from
dbo.SplitString(@receivedsetupPeriod,',') R
Join
dbo.SplitString(@currentperiod,',') C ON R.Id=C.Id
Join
dbo.SplitString(@toperiod,',') T ON T.Id=C.Id
Join
dbo.SplitString(@fromperiod,',') F ON T.Id=F.Id