CONVERTING EMBEDDED SQL WITH VARIABLE WHERE CLAUSE TO STORED PROCEDURE
Posted on 2011-05-12
I have several SQL Queries that are generated by ColdFusion WEB application and executed in the database. These queries take very long time to run, up to 2 minutes.
I want to convert them to SPs, however, I cannot figure out a way to handle the varying AND and OR conditions that make up the WHERE clause. That is, there may be different AND/OR conditions from one execution to another.
The code below is an example of the typical query:
select distinct TOP 501 a.mlsnum
from mls_unified_svo_tbl a (nolock) -- 46 sec, 18 rows
LEFT OUTER JOIN mls_unified_mvo_svo_tbl m (nolock) on a.mlsnum = m.mlsnum
LEFT OUTER JOIN photos_exist b (nolock) on a.mlsnum = b.mlsnum
LEFT OUTER JOIN virtual_tours c (nolock) on a.mlsnum = c.mlsnum
where ((a.status = 20 AND cast(a.statusdate as datetime) >= '03/26/2008')
OR (a.status = 25 AND cast(a.statusdate as datetime) >= '08/26/2008')
OR (a.status = 15 AND cast(a.statusdate as datetime) >= '08/26/2008')
OR (a.status IN (5,30,10))) AND (a.property_type = 0)
AND ( dbo.fn_GetDistance(34.051039, -118.341143, a.latitude, a.longitude) <= 0.25)