Cursors are Good

We use cursors in our data model, and it's essential to address the negative perception surrounding them, often linked with references to 'set theory' in various discussions..

The truth, although it might be a bit hard to accept, is that the performance of your data model might be subpar. This is a bold statement, and I'll explain why.

As our system evolves with more features, our tables grow, and our expectations from the same processes increase. For instance, consider a scenario where we want to generate JSON to populate a website. We might start with a simple query:

1
2
3SELECT someValue as Content
4FROM tbl_Component
5FOR JSON AUTO, INCLUDE_NULL_VALUES
6--[{"Content":"Hello"},{"Content":"World"}]
7
8

However, as our requirements become more complex, generating a JSON structure that meets the needs of UI developers for creating reusable components can be challenging. The traditional method might involve forcing the UI to piece the information together:

1
2
3SELECT Id, someValue as Content, ParentId  
4FROM tbl_Component
5LEFT JOIN tbl_Relationship on ComponentId = ParentId
6FOR JSON AUTO, INCLUDE_NULL_VALUES
7--[{"Id":23,"Content":"Hello","ParentId":null}
8--,{"Id":34,"Content":"World", "ParentId":23}]
9
10

In such cases, a more effective approach to build a proper JSON structure for UI purposes is through the use of recursive procedures employing cursors.

Consider the example of a procedure designed for generating JSON for a webpage:

1
2
3CREATE PROCEDURE prc_GetJSON_Webpage @In, @Out OUTPUT 
4AS
5BEGIN
6   DECLARE curJSON CURSOR LOCAL FAST_FORWARD
7   FOR
8      SELECT ComponentId
9      FROM tbl_Component
10   OPEN curJSON
11   FETCH NEXT FROM curJSON INTO @Id
12   WHILE @@FETCH_STATUS=0
13      BEGIN
14         EXEC prc_GetJSONWebpage @In = @In, @Out = @Out
15         FETCH NEXT FROM curJSON INTO @Id
16      END
17END
18
19

This approach, utilizing a cursor, often outperforms non-cursor alternatives, especially as the features change and evolve. It proves advantageous over the use of @table and #Table, as it does not require the same physical storage adjustments.

Additionally, the cursor approach allows for more flexibility in expanding the functionality of features, always ensuring that subsequent calls will land on a primary key or foreign key index. It creates a clear and easy-to-follow code path, contributing to the development of a feature-rich application.

1
2
3CREATE PROCEDURE prc_GetJSON_Webpage @In, @Out OUTPUT 
4AS
5BEGIN
6   DECLARE curJSON CURSOR LOCAL FAST_FORWARD
7   FOR
8      SELECT ComponentId, ProcessId, DisplayFlag, SecurityFlag
9      FROM tbl_Component
10   OPEN curJSON
11   FETCH NEXT FROM curJSON INTO @Id,@ProcessId,@DisplayFlag,@SecurityFlag
12   WHILE @@FETCH_STATUS=0
13      BEGIN
14         IF @SecurityFlag = 1
15            SELECT @SecurityPassed =  fnc_GetCheckSecurity_Context (@In)
16         IF @SecurityPassed = 0
17            RAISERROR()
18         IF @DisplayFlag = 1
19            EXEC prc_GetContent @In=@In, @Out = @ChildOut OUTPUT
20         IF @ProcessId IS NOT NULL 
21             EXEC prc_GetDynamicSQL_JSONArray @In=@In, @Out = @ChildOut OUTPUT
22         EXEC prc_GetJSONWebpage @In = @In, @Out = @ChildOut OUTPUT
23         SET @In = JSON_MODIFY(@In, @JSONName, @ChildOut)
24         FETCH NEXT FROM curJSON INTO @Id,@ProcessId,@DisplayFlag,@SecurityFlag
25      END
26END 
27
28

Cursors are Good

All I am saying is, just because you had a bad experience, doesn't mean they are bad.