TSQL query

Spunny 326 Reputation points
2023-07-11T14:33:05.8666667+00:00

Can you please give examples of CTE and cursor.

Thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,490 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 117.2K Reputation points MVP
    2023-07-11T21:20:24.1233333+00:00

    This query uses a CTE so that we can use the derived column table_name in the GROUP BY:

    ; WITH CTE AS (
       SELECT quotename(s.name) + '.' + quotename(o.name) AS table_name
       FROM   sys.objects o
       JOIN   sys.schemas s ON o.schema_id = s.schema_id
       JOIN   sys.columns c ON o.object_id = c.object_id
    )
    SELECT table_name, COUNT(*)
    FROM   CTE
    GROUP  BY table_name
    ORDER  BY table_name
    
    

    Here is a an example on how to write a cursor. Note that cursors is something you use only sparingly.

    DECLARE @cur CURSOR,
            @name sysname
    SET @cur = CURSOR STATIC FOR
       SELECT name FROM sys.objects ORDER BY name
    
    OPEN @cur
    
    WHILE 1 = 1
    BEGIN
       FETCH @cur INTO @name
       IF @@fetch_status <> 0
          BREAK
    
       PRINT @name
    END
    
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. PercyTang-MSFT 12,511 Reputation points Microsoft Vendor
    2023-07-12T02:35:16.6666667+00:00

    Hi @Spunny

    You can follow these two tutorials.

    https://www.sqlshack.com/sql-server-common-table-expressions-cte/

    https://www.sqlshack.com/sql-server-cursor-tutorial/

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Olaf Helper 46,031 Reputation points
    2023-07-12T05:25:04.9266667+00:00
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.