CODEWITHSUNDEEP
sqlsql-server
Oday Salim
Oday Salim

Reputation: 1147

If Exists Order By (SQL Server)

I am attempting to set up an SQL command to check if a SELECT statement would return values or not. If there are no rows to be returned, then I would like to print "None".

When I write a statement like this, it works:

if exists (select * from TEP_Payments_Table where [Project Name] = 'test')
(select * from TEP_Payments_Table where [Project Name] = 'test')
else
(select 'None')

However, when I add 'ORDER BY' then it fails:

if exists (select top(1) * from TEP_Payments_Table where [Project Name] = 'test' order by payid desc)
(select top(1) * from TEP_Payments_Table where [Project Name] = 'test' order by payid desc)
else
(select 'None')

Why is that?

It is important that I do the TOP(1) in my select to return the most recent value. I appreciate your help.

Upvotes: 2

Views: 573

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

EXISTS just validate your condition defined with WHERE clause therefor no need to use TOP (1) or ORDER BY clause :

You can directly express it as :

if exists (select 1 from TEP_Payments_Table where [Project Name] = 'test')
   begin
     select top (1) *
     from TEP_Payments_Table 
     where [Project Name] = 'test' 
     order by payid desc
   end
else 
   begin
      select 'non'
   end

Upvotes: 1

Max Szczurek
Max Szczurek

Reputation: 4334

Weird. SQL Server doesn't like those parentheses around the select statement on the 2nd line. This works:

if exists (select * from TEP_Payments_Table where [Project Name] = 'test')
    select top 1 [Project Name] from TEP_Payments_Table where [Project Name] = 'test' order by payid desc
else
    select 'None'

Upvotes: 3

Related Questions