TSQL (SSMS) conditionally display result of query only conditionally

Question

Is it possible to display in the results-window of a query in TSQL (SSMS) window conditionally?

For example, display column-header and result of:

SELECT COUNT(1) AS ourCount FROM [ourDatabase].[dbo].[ourTable]

only if it is > 0

NOTE: We use SQL Server 2008, r-2

This is in the context of a larger system of queries with many results. I don't want to clutter the results if this particular query has a zero-value. Of course, the concept could be generalized for other situations.

So I am monitoring the query output, and one could think of the results as an 'alert' to myself (informally).

SSMS is just a client tool. It doesn't affect how the queries are executed or what they produce. As for filtering data, that's done with the WHERE clause of a SELECT statement, or the HAVING() clause if you want to filter by an aggregate. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 4, 2018 at 13:28
I think this is an XY Problem. Why are you trying to accomplish the thing you're asking to do? By itself, it doesn't make much sense. — Eric Brandt
– Eric Brandt, Commented Oct 4, 2018 at 13:30
Richard's answer will do that for you, but for troubleshooting purposes, might you not want to know that a table is empty, especially if the expectation is that it won't be? I get the clutter thing, but having a consistent output has readability value, too. — Eric Brandt
– Eric Brandt, Commented Oct 4, 2018 at 13:36
@JosephDoggie there are far better options for monitoring than executing a script in SSMS. SSMS itself has several monitoring reports and dashboards. SQL Operations Studio, now called Azure Data Studio, allows you to convert queries to graphs and dashboards very easily. Check for example Customizing Dashboards. Several addins are already available for monitoring, including one that displays the statistics collected by running Brent Ozar's First Responder Kit — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 4, 2018 at 13:49

Richard Hansell · Accepted Answer · 2018-10-04 13:30:54Z

2

This will push the result into a variable, and then only display it if it's greater than zero, you could also use PRINT, etc.

DECLARE @Count INT;
SELECT @Count = COUNT(1) AS ourCount FROM [ourDatabase].[dbo].[ourTable];
IF @Count > 0
BEGIN
    SELECT @Count;
END;

If the answer is <= 0 then you will see nothing but the row count in the message part of SSMS. You can even stop this by adding:

SET NOCOUNT ON;

...at the top of your script, but remember to add:

SET NOCOUNT OFF;

answered Oct 4, 2018 at 13:30

Richard Hansell

5,4131 gold badge20 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JosephDoggie Over a year ago

Yes it works. For those not from the C-family of languages, the ';' is optional in the declare statement. Does COUNT(1) really work better than COUNT(*)? I guess so, it certainly is what I use, but I feel it is less readable, lots to say about such a small question!

Collectives™ on Stack Overflow

TSQL (SSMS) conditionally display result of query only conditionally

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related