Counting the number of records in a table is a common task in every application. It could be the number of users, orders, etc. The query itself in the simplest form would be:
SELECT COUNT(*) FROM [table];
While we could launch the query from the application's data access layer, it would normally be nice to have SQL codes in database in the form of stored procedures or functions. However, creating such module for each table would just grow its number linearly as the number of tables. With dynamic SQL, we could write one module that could be applied to all tables.
Though it might be useful only for a small subset of scenarios (since most scenarios require record filtering); it has dramatically reduced the number of functions you need to create by a factor of n; where n is the number of tables.
Note: ideally it would be a function but since we can't launch dynamic SQL inside it then stored procedure would just perform fine.
