T-SQL ENHANCEMENTS id balance – 2001 14 2001

T-SQL ENHANCEMENTS id balance – 2001 14 2001 12 2001 10 The balances 14, 12, and 10 correspond to the balances in the detail accounts 3001, 4001, and 4002, which are all decedents of account 2001. The query that follows the body of the CTE then groups the rows that are produced by account ID and calculates the balance with the SUMfunction. There are other ways to solve this problem without using CTEs. A batch that uses a stored procedure that calls itself or a cursor could produce the same result. However, the CTE is a query, and it can be used to define a view, something a stored procedure or a cursor-based batch cannot. The view definition that follows defines a view, which is the recursive query we used earlier, and then uses it to get the balance for a single account, account 2001. CREATE VIEW Rollup AS WITH Rollup(id, parent, balance) AS ( SELECT id, parent, balance FROM DetailAccount UNION ALL SELECT R1.id, R1.parent, R2.balance FROM RollupAccount R1 JOIN Rollup R2 ON R1.id = R2.parent ) SELECT id, SUM(balance) balance FROM Rollup GROUP ID id GO get the balance for account 2001 SELECT balance FROM rollup WHERE id = 2001 GO balance 36 (1 row(s) affected) One of the strengths of a recursive query is the fact that it is a query and can be used to define a view. In addition, a single query in SQL Server is always a transaction, which means that a recursive query based on a CTE is a transaction. Recursive queries, like any recursive algorithm, can go on forever. By default, if a recursive query attempts to do more than 100 recursions, it

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Cheap Web Hosting services

Bookmark the permalink.

Comments are closed.