CODEWITHSUNDEEP
sql-server
nathan
nathan

Reputation: 3

How can I return a message if there are no results from a query but display results if there are?

What is the best way to handle an if/else scenario where if 0 rows are returned do something, else display the data from a JOIN query?

I have this query:

SELECT 
    c.UserID AS 'Seller'
    ,a.ArtistName AS 'Artist Name'
    ,a.AlbumName AS 'Album Name'
    ,c.Price AS 'Sale Price'
    ,a.Format AS 'Album Format'
FROM COLLECTIONS c
JOIN #WantlistTemp t ON t.AlbumID = c.AlbumID
JOIN ALBUMS a ON a.AlbumID = c.AlbumID
WHERE SaleIndicator = 'Y'

and want to display a message if no records are returned else display the results.

Upvotes: 0

Views: 1557

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

Your client program will get a resultset with zero rows. And when you see that, display whatever message you want.

Upvotes: 1

Stu
Stu

Reputation: 32579

Assuming you want your query to always return a valid result set, a couple of solutions spring to mind.

You could first insert your data into a temp table and then return a specific result if there are no rows, ie

SELECT 
    c.UserID AS 'Seller'
    ,a.ArtistName AS 'Artist Name'
    ,a.AlbumName AS 'Album Name'
    ,c.Price AS 'Sale Price'
    ,a.Format AS 'Album Format'
into #Results
FROM COLLECTIONS c
JOIN #WantlistTemp t ON t.AlbumID = c.AlbumID
JOIN ALBUMS a ON a.AlbumID = c.AlbumID
WHERE SaleIndicator = 'Y'

if @@rowcount=0
  select <same column list with appropriate values to indicate 0 rows>
else
  select * from #Results

Another option to not use a temp table would be a simple exists check,

if exists (
  SELECT *
  FROM COLLECTIONS c
  JOIN #WantlistTemp t ON t.AlbumID = c.AlbumID
  JOIN ALBUMS a ON a.AlbumID = c.AlbumID
  WHERE SaleIndicator = 'Y'
)
<your actual query here>
else
<your 0 rows scenario select statement>

Upvotes: 0

Related Questions