Cursor Basics

-
When I started doing T-SQL, I remember Cursors being demonized and is said to be Evil by most of my colleagues, this made me shun away from using or even trying to understand how it works.

Finally, in one of the interviews  I was asked how would you retrieve hundred or maybe thousand records and iterate on each while validating each row and doing insert if a row is valid? 

My quick answer was to simply create a Table variable containing all the columns needed, plus an indexer column, do a INSERT INTO SELECT statement and also use  the Row_Number() Over syntax. After inserting all the records to the Table variable I now iterate on each row by doing a while loop and declaring an counter and making increments as it goes to the loop, and using the incremented value as where clause in the select statement.

This is how the code would look like..


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DECLARE @TempTable Table(Id Int, 
                         CustomerId varchar(10), 
                         CompanyName varchar(100), 
                         ContactName varchar(100), 
                         OrderId int, 
                         OrderDate datetime)
DECLARE @cntr int =0, @total int=0

INSERT INTO @TempTable(Id, 
                       CustomerId, 
        CompanyName, 
                       ContactName, 
        OrderId, 
        OrderDate)
SELECT ROW_NUMBER() OVER(ORDER BY c.CustomerId DESC) AS Id, 
       c.CustomerID, c.CompanyName, c.ContactName, OrderID, OrderDate
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID

SET @total =@@ROWCOUNT

WHILE(@cntr<=@total)
BEGIN

  SET @cntr = @cntr+1

  SELECT Id, CustomerId, CompanyName, ContactName, OrderId, OrderDate
 FROM @TempTable
 WHERE Id = @cntr 

END

Then the interviewer ask me why not use cursors? Will discuss this in including the Advantages and Disadvantages.


Comments

Post a Comment

Popular posts from this blog

Serializing JSON string to ExpandoObject

-
In some case you want to create and Object from JSON, a great way of doing this to deserialize this JSON to an ExpandoObject. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 /// <summary> /// Convert Json String to an Expando Object /// </summary> /// <param name="that">Json string to convert</param> /// <returns>Expando Object</returns> public static ExpandoObject ToExpando ( this string that) { ExpandoObject result; try { result = JsonConvert.DeserializeObject<ExpandoObject>(that, new ExpandoObjectConverter()); } catch (Exception e) { throw e; } return result; } Here we use JsonConvert.DeserializeObject, you need to add reference to NewtonSoft.Json see below. 1 2 3 using Newtonsoft.Json ; using Newtonsoft.Json.Converters ; using Newtonsoft.Json.Linq ;
Read more

XML to ExpandoObject using Recursion in C#

-
Here's how to convert XML to an ExpandoObject using recursion. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 public static dynamic ToExpando ( this XDocument document) { return CreateExpando (document.Root); } private static dynamic CreateExpando (XElement element) { var result = new ExpandoObject() as IDictionary< string , object >; if (element.Elements().Any(x => x.HasElements)) { var list = new List<ExpandoObject>(); result.Add(element.Name.ToString(), list); foreach ( var childElement in element.Elements()) { list.Add(CreateExpando(childElement)); } } else { foreach ( var leafElement in element.Elements()) { result.Add(leafElement.Name.ToString(), leafElement.Value); } } return result; } Happy Coding!
Read more

Automatically Discover and Assign Parameter with Values to a Stored Procedure Call in C#

-
I'm sure most programmers have run across a situation wherein they have to pass  a lot of parameters to a stored procedure call in C#. Instead of manually typing each parameter in and assigning a value, why not develop a way to automatically discover the parameter, add it to the parameter list of the command object and assign the corresponding value from an entity or model. Here's my implementation using extension method. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 /// <summary> /// Automatically assigns parameters to Command.Parameter Collection from List of parameter names /// </summary> /// <param name="that">Command object being extended</param> /// <param name="model">Expando model</param> /// <param name="conn">sQLConnection used to g...
Read more