Building Dynamic WHERE Clause in Procs

Question

Post reply

Building Dynamic WHERE Clause in Procs

rhunt

SSCertifiable

Points: 5868
More actions
September 14, 2005 at 2:21 pm

#102794

Just recently, I started a thread called "Strategies For Changing Where Clause in Procs". In this thread I asked questions about one way to deal with a changing WHERE clause in Procs. In that example I used the LIKE '%' argument as a default value.
Well, now I have a new proc (which I will include below) in which I am using a different method because I have one column that can have multiple possible values.
So, for this proc I used EXEC()...but I would like to know if others would have used sp_executesql for this proc in an attempt reuse cached plans.
If others out there might have used sp_executeSQL, please povide me with some pointers on how you would have written this string building process differently.
I haven't had to write too many of these dynamic procs, so I'm looking for any feedback that might be helpful to build an optimum proc here.
Thanks!
________________________________________________________________
EXEC dbo.spRyanSearchPropTest @StatusType='For Lease'
, @RegionID='843D1B88-4755-40BE-99B5-FAD28C4E7C85'
, @PropertyTypeIndustrial='Industrial'
, @SqFtRequested=10000
, @SqFtMultiplier=8
*/
CREATE PROCEDURE dbo.spRyanSearchPropTestWithLand
(@StatusType varchar(20)='',
@RegionID varchar(50)='',
@PropertyTypeOffice varchar(200)='',
@PropertyTypeRetail varchar(200)='',
@PropertyTypeIndustrial varchar(200)='',
@PropertyTypeLand varchar(200)='',
@SqFtMultiplier varchar(2)='',
@SqFtRequested varchar(20)='')
AS
BEGIN
declare @sql varchar(8000)
declare @SelectSQL varchar(8000)
declare @WhereSQL varchar(8000)
declare @LandSQL varchar(8000)
SET @sql=''
SET @SelectSQL=''
SET @WhereSQL=''
SET @LandSQL=''
IF @SqFtMultiplier = '' BEGIN SET @SqFtMultiplier=8 END
SET @SelectSQL=
'SELECT
b.[Name],
b.BuildingType,
a.PostOfficeBox,
a.Street,
a.Street2,
a.Street3,
ac.FirstName,
ac.LastName,
ac.PhoneNumber,
ac.PhoneExt,
s.SuiteNumber,
s.MinSqFt,
s.MaxSqFt
FROM dbo.Buildings b
LEFT OUTER JOIN dbo.Addresses a ON b.AddressID = a.AddressID
LEFT OUTER JOIN dbo.Actors ac ON b.LeasingAgentID = ac.ActorID
LEFT OUTER JOIN dbo.Suites s ON b.BuildingID = s.BuildingID'
SET @WhereSQL=
' WHERE b.StatusType='''+@StatusType
+''' AND b.RegionID='''+@RegionID+''''
IF (@PropertyTypeOffice<>'' OR @PropertyTypeRetail<>'' OR @PropertyTypeIndustrial<>'')
BEGIN
SET @WhereSQL=@WhereSQL
+' AND (b.BuildingType='''+@PropertyTypeOffice+''' OR b.BuildingType='''+@PropertyTypeRetail
+''' OR b.BuildingType='''+@PropertyTypeIndustrial+''' OR b.BuildingType='''+@PropertyTypeLand+''')'
END
IF @SqFtRequested<>''
BEGIN
SET @WhereSQL=@WhereSQL
+' AND s.MinSqFt BETWEEN ('+@SqFtRequested+'-('+@SqFtRequested+'*('+@SqFtMultiplier+'/100.00))) AND ('+@SqFtRequested+'+('+@SqFtRequested+'*('+@SqFtMultiplier+'/100.00)))'
+' AND s.MaxSqFt BETWEEN ('+@SqFtRequested+'-('+@SqFtRequested+'*('+@SqFtMultiplier+'/100.00))) AND ('+@SqFtRequested+'+('+@SqFtRequested+'*('+@SqFtMultiplier+'/100.00)))'
END
IF @PropertyTypeLand<>'' BEGIN
SET @LandSQL=
'UNION ALL
SELECT
l.[name],
NULL as BuildingType,
a.PostOfficeBox,
a.Street,
a.Street2,
a.Street3,
ac.FirstName,
ac.LastName,
ac.PhoneNumber,
ac.PhoneExt,
NULL as SuiteNumber,
NULL as MinSqFt,
NULL as MaxSqFt
FROM dbo.LandSites l
LEFT OUTER JOIN dbo.Addresses a ON l.AddressID = a.AddressID
LEFT OUTER JOIN dbo.Actors ac ON l.LeasingAgent = ac.ActorID
WHERE RegionID='''+@RegionID+''''+' AND StatusType='+''''+@StatusType+''''
END
SET @sql=@SelectSQL+@WhereSQL+@LandSQL
EXEC (@SQL)
--PRINT @sql
END
GO
________________________________________________________________