ALTER PROCEDURE [dbo].[ps_Residential_SEARCH]
---Declare input variables
@OrderBy nvarchar(50)= NULL, --column name to odrder by
@OrderByDir nvarchar(1) = NULL, --Direction of sort 'A' = ascending 'D' = Descending
@intCustomerID int = NULL,
@intCompanyID int = NULL,
@dtServiceStart datetime = NULL,
@dtServiceEnd datetime = NULL,
@chrBuildingNum nvarchar(15)= NULL,
@chrRoad nvarchar(40)= NULL,
@chrSuite nvarchar(6) = NULL,
@chrAlternate nvarchar(50)= NULL,
@intRegionID int = NULL,
@intAddressTypeID int = NULL,
@blnErequest bit = NULL,
@blnSignsAllowed bit = NULL,
@blnStatus bit = NULL,
@blnBill bit = NULL,
@LocalRows int = 0 OUTPUT,
@ReturnValue int = 0 OUTPUT,
@LocalError int = 0 OUTPUT,
@OutMessage nvarchar(500) OUTPUT
AS
BEGIN TRY
---SET NOCOUNT ON added to prevent extra result sets from
---interfering with SELECT statements.
SET NOCOUNT ON;
---Declare local scope variables
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
---Create the SQL string that will be dynamic
SELECT @sql = 'SELECT dbo.Residential.intResidential, dbo.Company.intCompanyID, dbo.Residential.intCustomerID, dbo.Customer.chrLastName + '', '' + dbo.Customer.chrFirstName AS chrCustomer,
dbo.Company.chrName + '' '' + ISNULL(dbo.Company.chrBranch, '''') AS chrCompany, dbo.AddressLkup.intServiceID,
dbo.ResidentialService.chrServiceCode, dbo.AddressLkup.chrPrefix, dbo.AddressLkup.chrSuite, dbo.Address.intAddressID, dbo.Address.chrBuildingNum, dbo.Address.chrRoad,
dbo.Address.intRegionID, dbo.Address.intQuadrantID, dbo.Address.intAddressTypeID, dbo.Address.intZoneID, dbo.ResidentialZone.intMLS,
dbo.AddressType.chrName, dbo.Address.chrAlternate, dbo.Address.blnSignsAllowed, dbo.Residential.blnBill, dbo.Residential.blnStatus,
dbo.Residential.dtServiceDate, dbo.Residential.intUserId, dbo.fx_FORMAT_Address(dbo.AddressLkup.chrSuite, dbo.Address.chrBuildingNum, dbo.Address.chrRoad,
dbo.AddressRegion.chrRegion, dbo.AddressQuadrant.chrDisplay) AS chrAddress
FROM dbo.ResidentialZone RIGHT OUTER JOIN
dbo.Address ON dbo.ResidentialZone.intZoneID = dbo.Address.intZoneID LEFT OUTER JOIN
dbo.AddressType ON dbo.Address.intAddressTypeID = dbo.AddressType.intAddressTypeID LEFT OUTER JOIN
dbo.AddressQuadrant ON dbo.Address.intQuadrantID = dbo.AddressQuadrant.intQuadrantID RIGHT OUTER JOIN
dbo.AddressLkup ON dbo.Address.intAddressID = dbo.AddressLkup.intAddressID LEFT OUTER JOIN
dbo.ResidentialService ON dbo.AddressLkup.intServiceID = dbo.ResidentialService.intServiceID RIGHT OUTER JOIN
dbo.ResidentialTransaction RIGHT OUTER JOIN
dbo.Residential ON dbo.ResidentialTransaction.intResidentialTransID = dbo.Residential.intResidentialTrans ON
dbo.AddressLkup.intResidentialID = dbo.Residential.intResidential LEFT OUTER JOIN
dbo.Customer ON dbo.Residential.intCustomerID = dbo.Customer.intCustomerID LEFT OUTER JOIN
dbo.Company ON dbo.Customer.intCompanyID = dbo.Company.intCompanyID LEFT OUTER JOIN
dbo.AddressRegion ON dbo.Address.intRegionID = dbo.AddressRegion.intRegionID
WHERE 1 = 1'
---Use is null to determin if parameter was passed, if so append to SQL string...
IF @intCustomerID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Customer.intCustomerID = @intCustomerID'
IF @intCompanyID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Company.intCompanyID = @intCompanyID'
IF @dtServiceStart IS NOT NULL AND @dtServiceEnd IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Residential.dtServiceDate >= @dtServiceStart AND dbo.Residential.dtServiceDate < @dtServiceEnd + 1'
IF @dtServiceStart IS NOT NULL AND @dtServiceEnd IS NULL
SELECT @sql = @sql + ' AND dbo.Residential.dtServiceDate >= @dtServiceStart AND dbo.Residential.dtServiceDate < @dtServiceStart + 1'
IF @chrBuildingNum IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Address.chrBuildingNum LIKE ''%'' + @chrBuildingNum + ''%'''
IF @chrRoad IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Address.chrRoad LIKE ''%'' + @chrRoad + ''%'''
IF @chrSuite IS NOT NULL
SELECT @sql = @sql + ' AND dbo.AddressLkup.chrSuite LIKE ''%'' + @chrSuite + ''%'''
IF @chrAlternate IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Address.chrAlternate LIKE ''%'' + @chrAlternate + ''%'''
IF @intRegionID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Address.intRegionID = @intRegionID'
IF @intAddressTypeID IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Address.intAddressTypeID = @intAddressTypeID '
IF @blnErequest IS NOT NULL
BEGIN
IF @blnErequest = (1)
SELECT @sql = @sql + ' AND dbo.Residential.intUserId > 0'
IF @blnErequest = 0
SELECT @sql = @sql + ' AND (dbo.Residential.intUserId = 0 OR dbo.Residential.intUserId IS NULL) '
END
IF @blnSignsAllowed IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Address.blnSignsAllowed = @blnSignsAllowed '
IF @blnStatus IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Residential.blnStatus = @blnStatus '
IF @blnBill IS NOT NULL
SELECT @sql = @sql + ' AND dbo.Residential.blnBill = @blnBill '
---Set the order by staement
IF @OrderBy IS NOT NULL
SELECT @sql = @sql + ' ORDER BY' +
CASE
WHEN @OrderBy = 'Realtor' AND @OrderByDir = 'D' THEN ' chrCustomer DESC'
WHEN @OrderBy = 'Realtor' AND @OrderByDir != 'D' THEN ' chrCustomer'
WHEN @OrderBy = 'Company' AND @OrderByDir = 'D' THEN ' chrCompany DESC'
WHEN @OrderBy = 'Company' AND @OrderByDir != 'D' THEN ' chrCompany'
WHEN @OrderBy = 'Date' AND @OrderByDir = 'D' THEN ' dbo.Residential.dtServiceDate DESC'
WHEN @OrderBy = 'Date' AND @OrderByDir != 'D' THEN ' dbo.Residential.dtServiceDate'
WHEN @OrderBy = 'Building' AND @OrderByDir = 'D' THEN ' dbo.Address.chrBuildingNum DESC'
WHEN @OrderBy = 'Building' AND @OrderByDir != 'D' THEN ' dbo.Address.chrBuildingNum'
WHEN @OrderBy = 'Street' AND @OrderByDir = 'D' THEN ' dbo.Address.chrRoad DESC'
WHEN @OrderBy = 'Street' AND @OrderByDir != 'D' THEN ' dbo.Address.chrRoad'
WHEN @OrderBy = 'Region' AND @OrderByDir = 'D' THEN ' dbo.AddressRegion.chrRegion DESC'
WHEN @OrderBy = 'Region' AND @OrderByDir != 'D' THEN ' dbo.AddressRegion.chrRegion'
WHEN @OrderBy = 'Active' AND @OrderByDir = 'D' THEN ' dbo.Residential.blnStatus DESC'
WHEN @OrderBy = 'Active' AND @OrderByDir != 'D' THEN ' dbo.Residential.blnStatus'
---Use default sort order for returned recordset.
ELSE ' dbo.Residential.dtServiceDate DESC'
END
ELSE
---Use default sort order for returned recordset.
SELECT @sql = @sql + ' ORDER BY dbo.Residential.dtServiceDate DESC'
----Set the parameter list for the sp_executesql call
SELECT @paramlist = ' @OrderBy nvarchar(50),
@OrderByDir nvarchar(1),
@intCustomerID int,
@intCompanyID int,
@dtServiceStart datetime,
@dtServiceEnd datetime,
@chrBuildingNum nvarchar(15),
@chrRoad nvarchar(40),
@chrSuite nvarchar(6),
@chrAlternate nvarchar(50),
@intRegionID int,
@intAddressTypeID int,
@blnErequest bit,
@blnSignsAllowed bit,
@blnStatus bit,
@blnBill bit'
---Execute the function sp_executesql that takes the following parameters:
--- sql string, parameter list, and then the declared parameters in the same order as the parmaeter list
EXEC sp_executesql @sql, @paramlist, @OrderBy, @OrderByDir, @intCustomerID, @intCompanyID, @dtServiceStart, @dtServiceEnd, @chrBuildingNum, @chrRoad, @chrSuite, @chrAlternate, @intRegionID, @intAddressTypeID, @blnErequest, @blnSignsAllowed, @blnStatus, @blnBill
---if no errors, set the return value to 0 and return the number of rows in the search results
SELECT @LocalRows = @@ROWCOUNT, @ReturnValue = 0
SELECT @LocalRows, @ReturnValue
END TRY
BEGIN CATCH
---If errors occured - capture error information and return to calling application
SELECT @ReturnValue = 1, @LocalError = ERROR_NUMBER(), @OutMessage = ('ROWS AFFECTED = ' + CAST(@@ROWCOUNT as nvarchar(6)) + ' SEVERITY = ' + CAST(ERROR_SEVERITY() as nvarchar(3)) + ' STATE = ' + CAST(ERROR_STATE() as nvarchar(2)) + ' PROCEDURE = ' + ERROR_PROCEDURE() + ' LINE = ' + CAST(ERROR_LINE() as nvarchar(3)) + ' MESSAGE ' + ERROR_MESSAGE())
SELECT @LocalError, @ReturnValue, @OutMessage, ERROR_MESSAGE(),ERROR_PROCEDURE(),ERROR_LINE()
END CATCH
