CODEWITHSUNDEEP
sql-server
user1037747
user1037747

Reputation: 1373

CAST statement with expression in SQL Server

I am trying to write a SQL select which has this condition:

I want to use a CASE statement only

Below is my query 

declare @distance int
set @distance  = 3000

select 
    ROUND((dbo.GetDistance(@UserLat, @UserLon, Latitude, Longitude) * 1000), 0) AS Distance 
from 
    messages     
order by 
    Distance desc

Upvotes: 0

Views: 76

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

SELECT *
FROM (
   SELECT *, 
          ROUND((dbo.GetDistance(@UserLat, @UserLon, Latitude, Longitude)*1000),0) AS Distance 
   FROM messages ) s
WHERE (@distance IS NULL) OR (s.Distance < @distance)
ORDER BY Distance DESC

If @distance is null then all rows of messages will be returned, else only rows having s.Distance < @distance are selected.

Upvotes: 1

Related Questions