Cursor in SQL Server is a control structure which process data row by row. It enables rows in a result to process sequentially. SQL Cursor is a type of iterator which can be used to process any record of table & can modify records according to our needs.
To use cursors in SQL procedures, you need to do the following:
- Declare a cursor that defines a result set.
- Open the cursor to establish the result set.
- Fetch the data into local variables as needed from the cursor, one row at a time.
- Close and de-allocate the cursor when done.
For simple example of SQL Cursor, I used below example. I used table that I used in earlier post Delete Duplicate Rows in SQL.
DECLARE @EMailId Varchar(50)
DECLARE @getEMailId CURSOR
SET @getEMailId = CURSOR FOR
SELECT EMail FROM [User]
FROM @getEMailId INTO @EMailId
WHILE @@FETCH_STATUS = 0
FROM @getEMailId INTO @EMailId
In above example, I simple select column EMail of user table. I assign is to @EMailId variable. Then Printing current row of SQL cursor one by one.!
SQL Cursor processes records one by one, so it consumes lots of memory & time. So use of cursor should be minimal.!
In earlier post, I published article about Find duplicate rows in SQL. Now It’s easy when you have small amount of duplicate data. But in million records,it’s very difficult to delete duplicate rows in SQL. Continue with older post, I have added one more duplicate Email.My table will look like below.! Now what I want is, […] Continue Reading →
Order By clause is used to order a number or string of any column of SQL. We can esaily order any column if it;s data-type is INTEGER.Suppose we have column contain data like 2A,1,4,3B,11,111 in VARCHAR column and need to order this column.So below SQL tutorial is about Order by String and number in SQL […] Continue Reading →
Many times we want to block IP Address so particular machine is not able to access website/blog again. People may be hackers,enemies,Invalid clickers or may be another.Below is guide to block IP address from website. Login to Cpanel Account Almost all Hosting providers offers you cPanel.Login to your cPanel,for my example I use BlueHost here. Go to […] Continue Reading →
As a good programmer,you need to use many Date functions in SQL Server.I share here most used Date functions of SQL Server which will do different functionality according to its needs. GETDATE() You can easily retrieve current Date Time in SQL Server using GETDATE() function. Query : select GETDATE() CONVERT() Many times,We want to convert Date Format […] Continue Reading →
A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.(Source: MSDN).To create stored procedure in SQL Server,Go to Database ->Programmability->Stored Procedures.Click right click and click on New Stored Procedure.It will create a new Query Window for you. You will get above image script in your SQL Server.In this script,you have to […] Continue Reading →
Sometimes we need to insert – save multiple checked checkbox values in SQL Server.For that we can use Split function. Split function of SQL Server is not in-built function,We have to add it manually in our Table-valued function as you can see in below image. Split function in SQL Server Below you can find query […] Continue Reading →
Many times,We want to covert rows of SQL table into comma separated string which works as value.You can get comma separated values in SQL Server using COALESCE function or without it. I hope you have created table,otherwise in my example check Case Statement in SQL Server for to create & insert scripts for Product Table. After creating Product […] Continue Reading →
In this SQL tutorial,I will explain about How to update multiple rows in SQL Server with single SQL query? Before you read this article.You should have knowledge of Case statement.I wrote about How to use Case statement in my previous post. Check Case statement in SQL If you also want to make tutorial on your localhost,You […] Continue Reading →
Case When statement in SQL server is very much similar like switch case statement in C.Below is SQL Case When statement syntax. Simple CASE statement in SQL
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
To understand Case expression in SQL,here is example of Case statement .First of I create table of product.It contains ProductId(Auto increment,Primary key),ProductName,Price,Availability columns. You can use below script […] Continue Reading →