I have a situation where on a dashboard, for pending approvals I am trying to show certain items as follows
- Item 1 [Count]
- Item 2 [Count]
- Item 3 [Count]
The [Count] shows a numeric value of items pending approval. On click of each of these items, there is an associated table where the records are being shown.
The way of deriving these counts is very complex and I wish to avoid making duplicate queries for count for example query #1 as
SELECT COUNT(*)
FROM tableName
and then query #2 as
SELECT ColumnA, ColumnB, ColumnC
FROM tableName
Since these queries are being read into my C# application, until now I've been doing the following
var onlyCount = true;
var subQuery = onlyCount? "COUNT(*)": "ColumnA, ColumnB, ColumnC";
var query = $"SELECT {subQuery} FROM tableName";
But with an ever-growing list of columns that needs to be managed, this makes the code look ugly. With calculated data in the select list, Case(s), IIF(s) in the statement the above-said solution is no longer a "maintainable" solution. With the select query is something as demonstrated below even possible?
DECLARE @CountOnly AS BIT = 1
SELECT
CASE
WHEN @CountOnly = 1
THEN COUNT(*)
ELSE ColumnA, ColumnB, ColumnC
END
FROM
tableName
Have any one ever faced such a scenario? Or if you could point me in a direction where this can be handled better?
Side note: The above query is being passed into a SqlDataReader
to fetch the data and show to the end user.
CodePudding user response:
You may want to use something like this:
DECLARE @CountOnly AS BIT = 1
IF (@CountOnly = 1)
BEGIN
SELECT ColumnA, ColumnB, ColumnC
FROM MyTable
ELSE
SELECT COUNT(*)
FROM MyTable
END