Stored Procedure code debate
-
we have a discussion between dveloppers and dbas here regarding a procedure that does a complexe search into a database where the parmaters are optional. We wanted to know if the approach below is optimal and if not how would we proceed.
CREATE PROCEDURE dbo.cp_Entity_Find_AdvanceSearch
(
@entityList varchar(2000) = '',
@entityRole int = 0,
@entityType int = -1,
@searchCompleteName varchar(240) = '',
@searchFirstName varchar(50) = '',
@searchLastName varchar(50) = '',
@searchAddress varchar(50) = '',
@phoneNumber varchar(15) = '',
@city varchar(25) = '',
@provinceID int = -1,
@countryID int = -1,
@searchScope int = 0,
@searchType int = 0,
@postalCode varchar(10) = '',
@partialSearch int = 0,
@partialSearchField int = -1,
@searchNameInHistory int = 0,
@searchNameInNDC int = 0,
@searchAddressInHistory int = 0,
@searchInArchived int = 0,
@alphaCode varchar(255) = '',
@originatingUnitID int = -1,
@administrativeUnitID int = -1,
@BDCRepresentativeMgtUnitID int = -1,
@BDCRepresentativeID int = -1,
@keywordType int = -1,
@keyword varchar(100) = '',
@gcProjectNumber varchar(255) = '',--TODO: To be implemented in delivery 5. Confirmer la longueur du champ
@advancedSearchType int = -1,
@languageCode int = 102001,
@userCode varchar(64),
@trueRowCount int = 0 OUTPUT)
/*
/// <procedure>
/// cp_Entity_Find_AdvanceSearch
/// </procedure>
///
/// <summary>
/// This stored procedure return information to fill the grid when the search is called.
/// An effort has been made to keep to SP compiled. This is why there is some logic to
/// initialize some variables at the beginning so a condition will always return true
/// if not applied.
///
/// There is two way to search on alphanumeric column. The regular one is base on the contains
/// clause of SQL. The second can be applied only on one colunm at a time and can search
/// for multiple word within the colunm value. This method is based on the like clause of SQL
/// by replacing all spaced by of '%'. For example, if the user enters 'abc xyz' for the adddress,
/// the selection clause will be the following: SEARCHABLEADDRESS LIKE '%abc%xyz%'
/// </summary>
///
/// <param>
/// @entityList: The list of entity primary key on wich to limit the search. No restriction if empty.
/// @entityRole: A binary variable to applied on four columns of the database
/// @entityType: The entity type: business, individual or all to search for
/// @searchCompleteName: The name to search for
/// @searchFirstName: The first name to search for
/// @searchLastName: The last name to search for
/// @searchAddress: The address to search for
/// @phoneNumber: The phone number to search for
/// @city: The city to search for
/// @provinceID: The province primary key to search for
/// @countryID: The country primary key to search for
/// @searchScope: The search scope. True to search in user favorite table
/// @searchType: The search type: 0 = all words, 1 = any wors, 2 = exact phrase
/// @postalCode: The postal code to search for
/// @partialSearch: True to applied a partial search on a specific field
/// @partialSearchField: The field on wich to applied the partial search
/// @searchNameInHistory: True to search in the historic for the name
/// @searchNameInNDC: True to select non detailed contact entity
/// @searchAddressInHistory: True to search in the historic for the address
/// @searchInArchived: True to search archived entity
/// @alphaCode: The alpha code to search for
/// @originatingUnitID: The originating unit primary key to search for
/// @administrativeUnitID: The administrative unit primary key to search for
/// @BDCRepresentativeMgtUnitID: The BDC Representative management unit primary key to search for
/// @BDCRepresentativeID: The BDC Representative primary key to search for
/// @keywordType: The type of the keyword on wich to apply the search
/// @keyword: The keyword to search for
/// @gcProjectNumber: The project number to search for
/// @advancedSearchType: The column on wich to apply the search
/// @languageCode: The language code
/// @userCode: NT user logon
/// @trueRowCount: This is an output parameter. It returns the number of entity selected.
/// </param>
///
/// <returns>
/// This stored procedure return information to fill the grid when the search is called.
/// </returns>
///
/// <execsample>
/// EXECUTE cp_Entity_Find_AdvanceSearch ...
/// </execsample>
///
/// <history>
///
/// NOTE: Even if this stored procedure has been created on the 2003-06-03. The history is kept
/// because it has been derived from the cp_Entity_Find.
///
/// DateResourceModification
/// 2003-04-29Stephen MandevilleCreation of Stored Procedure
/// 2003-05-08Mario FontaineImplement the search criteria according to the specifications.
///The Stored Proc was originally returning all the records of the table Entity
/// 2003-05-09Mario FontaineAdd the columns AddressLine1, AddressLine2, ProvinceID, CountryID and PostalCode in the SELECT clause
/// 2003-05-15Stephen MandevilleAdded @trueRowCount support
/// 2003-05-21Mario FontaineIf we receive 0 in the provinceID parameter, this mean all provinces
/// 2003-06-03Mario FontaineTransfer the construction of different clauses to this SP
/// 2003-06-06Mario FontaineUse the OR operator between entityRole values
/// 2003-08-27Stephen MandevilleModified SearchableName to SearchableCompleteName
/// 2003-09-02Mario FontaineAdd the parameters for the advanced search and modify the logic to
///execute the query directly instead of building it dynamically
/// </history>
*/
AS
SET NOCOUNT ON
-- Variables used for the contains statement
DECLARE @SearchableNameContainsString varchar(500)
DECLARE @SearchableFirstNameContainsString varchar(250)
DECLARE @SearchableLastNameContainsString varchar(250)
DECLARE @SearchableAddressContainsString varchar(250)
DECLARE @SearchableCityContainsString varchar(200)
-- Variables used for the like when the partial search is specified
DECLARE @searchNameLikeString varchar(500)
DECLARE @searchFirstNameLikeString varchar(250)
DECLARE @searchLastNameLikeString varchar(250)
DECLARE @searchAddressLikeString varchar(250)
DECLARE @searchCityLikeString varchar(200)
DECLARE @searchPhoneNumberLikeString varchar(30)
DECLARE @searchPostalCodeLikeString varchar(20)
DECLARE @searchAlphaCodeLikeString varchar(500)
DECLARE @searchKeywordLikeString varchar(200)
-- Other variables
DECLARE @retCode int
DECLARE @operator varchar(6)
DECLARE @BUSINESS int
DECLARE @INDIVIDUAL int
SELECT @retCode = @@ERROR
IF @retCode = 0
BEGIN
SET @BUSINESS = 162001
SET @INDIVIDUAL = 162002
-- Validation of the parameters
IF @entityType > 0 AND @entityType <> @BUSINESS AND @entityType <> @INDIVIDUAL
BEGIN
RAISERROR ('The entity type is not valid.', 10, 1)
RETURN
END
-- Validation of the parameters
IF @searchType <> 0 AND @searchType <> 1 AND @searchType <> 2 AND @searchType <> 3
BEGIN
RAISERROR ('The search type is not valid.', 10, 1)
RETURN
END
SET @searchCompleteName = LTRIM(RTRIM(@searchCompleteName))
SET @searchFirstName = LTRIM(RTRIM(@searchFirstName))
SET @searchLastName = LTRIM(RTRIM(@searchLastName))
SET @searchAddress = LTRIM(RTRIM(@searchAddress))
SET @city = LTRIM(RTRIM(@city))
SET @searchNameLikeString = '%' + REPLACE(@searchCompleteName, ' ', '%') + '%'
SET @searchFirstNameLikeString = '%' + REPLACE(@searchFirstName, ' ', '%') + '%'
SET @searchLastNameLikeString = '%' + REPLACE(@searchLastName, ' ', '%') + '%'
SET @searchAddressLikeString = '%' + REPLACE(@searchAddress, ' ', '%') + '%'
SET @searchCityLikeString = '%' + REPLACE(@city, ' ', '%') + '%'
SET @searchPhoneNumberLikeString = '%' + REPLACE(LTRIM(RTRIM(@phoneNumber)), ' ', '%') + '%'
SET @searchPostalCodeLikeString = '%' + REPLACE(LTRIM(RTRIM(@postalCode)), ' ', '%') + '%'
SET @searchAlphaCodeLikeString = '%' + REPLACE(LTRIM(RTRIM(@alphaCode)), ' ', '%') + '%'
SET @searchKeywordLikeString = '%' + REPLACE(LTRIM(RTRIM(@keyword)), ' ', '%') + '%'
SET @SearchableNameContainsString = '"' + @searchCompleteName + '"'
SET @SearchableFirstNameContainsString = '"' + @searchFirstName + '"'
SET @SearchableLastNameContainsString = '"' + @searchLastName + '"'
SET @SearchableAddressContainsString = '"' + @searchAddress + '"'
SET @SearchableCityContainsString = '"' + @city + '"'
-- According to the search type, the separator between each word (a single space), is replace
-- by an "AND" or "OR" logical expression
IF @searchType = 0 --All words (AND)
BEGIN
SET @SearchableNameContainsString = REPLACE(@SearchableNameContainsString, ' ', '" AND "')
SET @SearchableFirstNameContainsString = REPLACE(@SearchableFirstNameContainsString, ' ', '" AND "')
SET @SearchableLastNameContainsString = REPLACE(@SearchableLastNameContainsString, ' ', '" AND "')
SET @SearchableAddressContainsString = REPLACE(@SearchableAddressContainsString, ' ', '" AND "')
SET @SearchableCityContainsString = REPLACE(@SearchableCityContainsString, ' ', '" AND "')
SET @operator = 'AND'
END
ELSE
BEGIN
IF @searchType = 1 --Any words (OR)
BEGIN
SET @SearchableNameContainsString = REPLACE(@SearchableNameContainsString, ' ', '" OR "')
SET @SearchableFirstNameContainsString = REPLACE(@SearchableFirstNameContainsString, ' ', '" OR "')
SET @SearchableLastNameContainsString = REPLACE(@SearchableLastNameContainsString, ' ', '" OR "')
SET @SearchableAddressContainsString = REPLACE(@SearchableAddressContainsString, ' ', '" OR "')
SET @SearchableCityContainsString = REPLACE(@SearchableCityContainsString, ' ', '" OR "')
SET @operator = 'OR'
END
-- ELSE
--@searchType = 3 --Exact phrase
--Nothing to do. The searchable string are already initialized
END
--------------------------------------------------------------------------
-- Exécution du select
-- ATTENTION: toute modification dans la requete du select entraine
-- la meme modification dans la requete du count et vice-versa
--------------------------------------------------------------------------
-- Initialisation of the SELECT clause
SELECT DISTINCT
e.EntityID,
dbo.fn_IsFavorite(e.EntityID, @userCode) IsFavorite,
EntityName =
CASE
WHEN EntityTypeID = @BUSINESS THEN e.BusinessName
WHEN EntityTypeID = @INDIVIDUAL THEN (e.FirstName + ' ' + COALESCE(e.MiddleName + ' ','') + e.LastName)
END,
e.TradeName,
e.City,
br.IsProfessional,
DescrEN AS EntityStatusDescrEN,
DescrFR AS EntityStatusDescrFR,
e.ModificationDate,
e.AddressLine1,
e.AddressLine2,
e.ProvinceID,
e.CountryID,
e.PostalCode
FROM entity e
LEFT JOIN TelephoneNumbertON e.EntityID = t.EntityID
LEFT JOIN UserFavoriteuON e.EntityID = u.EntityID
LEFT JOIN view_EntityBusinessRulesbrON e.EntityID = br.EntityID
LEFT JOIN EntityManagementUnitemuON e.EntityID = emu.EntityID
LEFT JOIN EntityManagementUnitemu2ON e.EntityID = emu2.EntityID
LEFT JOIN RepresentativeRolerrON e.EntityID = rr.EntityID
LEFT JOIN RepresentativeRolerr2ON e.EntityID = rr2.EntityID
LEFT JOIN KeywordkON e.EntityID = k.EntityID
LEFT JOIN EntityNameenON e.EntityID = en.EntityID
LEFT JOIN EntityAddresseaON e.EntityID = ea.EntityID
LEFT JOIN BDCLookuplON br.EntityStatusID = l.LookupID
WHERE
(@entityType < 0 OR EntityTypeID = @entityType)
AND((@searchCompleteName = '' OR @entityType = @INDIVIDUAL)
-- Normal search on the name and trade name
OR ((@entityType = @BUSINESS OR @entityType < 0) AND @searchCompleteName <> '' AND (@partialSearch = 0 OR @partialSearchField <> 198001) AND (CONTAINS(e.SearchableCompleteName, @SearchableNameContainsString) OR CONTAINS(e.SearchableTradeName, @SearchableNameContainsString)))
-- Partial search on the name and trade name
OR ((@entityType = @BUSINESS OR @entityType < 0) AND @searchCompleteName <> '' AND @partialSearch = 1 AND @partialSearchField = 198001 AND (e.SearchableCompleteName LIKE @searchNameLikeString OR e.SearchableTradeName LIKE @searchNameLikeString))
-- Normal search on the name and trade name in the history table
OR ((@entityType = @BUSINESS OR @entityType < 0) AND @searchCompleteName <> '' AND (@partialSearch = 0 OR @partialSearchField <> 198001) AND @searchNameInHistory > 0 AND (@searchNameInNDC > 0 OR en.NameTypeID <> 196005) AND en.EndDate IS NOT NULL AND CONTAINS(en.SearchableName, @SearchableNameContainsString))
-- Partial search on the name and trade name in the history table
OR ((@entityType = @BUSINESS OR @entityType < 0) AND @searchCompleteName <> '' AND @partialSearch = 1 AND @partialSearchField = 198001 AND @searchNameInHistory > 0 AND (@searchNameInNDC > 0 OR en.NameTypeID <> 196005) AND en.EndDate IS NOT NULL AND en.SearchableName LIKE @searchNameLikeString))
AND((@searchLastName = '' OR @entityType <> @INDIVIDUAL)
-- Normal search on the last name
OR (@entityType = @INDIVIDUAL AND @searchLastName <> '' AND (@partialSearch = 0 OR @partialSearchField <> 199001) AND (CONTAINS(SearchableLastName, @SearchableLastNameContainsString)))
-- Partial search on the last name
OR (@entityType = @INDIVIDUAL AND @searchLastName <> '' AND @partialSearch = 1 AND @partialSearchField = 199001 AND SearchableLastName LIKE @searchLastNameLikeString)
-- Normal search on the last name in the history table
OR (@entityType = @INDIVIDUAL AND @searchLastName <> '' AND (@partialSearch = 0 OR @partialSearchField <> 199001) AND @searchNameInHistory > 0 AND (@searchNameInNDC > 0 OR en.NameTypeID <> 196005) AND en.EndDate IS NOT NULL AND CONTAINS(SearchableName, @SearchableLastNameContainsString))
-- Partial search on the last name in the history table
OR (@entityType = @INDIVIDUAL AND @searchLastName <> '' AND @partialSearch = 1 AND @partialSearchField = 199001 AND @searchNameInHistory > 0 AND (@searchNameInNDC > 0 OR en.NameTypeID <> 196005) AND en.EndDate IS NOT NULL AND en.SearchableName LIKE @searchLastNameLikeString))
AND((@searchFirstName = '' OR @entityType <> @INDIVIDUAL)
-- Normal search on the first name
OR (@entityType = @INDIVIDUAL AND @searchFirstName <> '' AND (@partialSearch = 0 OR @partialSearchField <> 199002) AND (CONTAINS(SearchableFirstName, @SearchableFirstNameContainsString)))
-- Partial search on the first name
OR (@entityType = @INDIVIDUAL AND @searchFirstName <> '' AND @partialSearch = 1 AND @partialSearchField = 199002 AND SearchableFirstName LIKE @searchFirstNameLikeString)
-- Normal search on the first name in the history table
OR (@entityType = @INDIVIDUAL AND @searchFirstName <> '' AND (@partialSearch = 0 OR @partialSearchField <> 199002) AND @searchNameInHistory > 0 AND (@searchNameInNDC > 0 OR en.NameTypeID <> 196005) AND en.EndDate IS NOT NULL AND CONTAINS(SearchableName, @SearchableFirstNameContainsString))
-- Partial search on the first name in the history table
OR (@entityType = @INDIVIDUAL AND @searchFirstName <> '' AND @partialSearch = 1 AND @partialSearchField = 199002 AND @searchNameInHistory > 0 AND (@searchNameInNDC > 0 OR en.NameTypeID <> 196005) AND en.EndDate IS NOT NULL AND en.SearchableName LIKE @searchFirstNameLikeString))
AND((@City = '')
-- Normal search on the city
OR (@City <> '' AND (@partialSearch = 0 OR (@partialSearchField <> 198003 AND @partialSearchField <> 199004)) AND (CONTAINS(e.SearchableCity, @SearchableCityContainsString)))
-- Partial search on the city
OR (@City <> '' AND @partialSearch = 1 AND (@partialSearchField = 198003 OR @partialSearchField = 199004) AND e.SearchableCity LIKE @searchCityLikeString)
-- Normal search on the city in the history table
OR (@City <> '' AND (@partialSearch = 0 OR (@partialSearchField <> 198003 AND @partialSearchField <> 199004)) AND @searchAddressInHistory > 0 AND ea.EndDate IS NOT NULL AND (CONTAINS(ea.SearchableCity, @SearchableCityContainsString)))
-- Partial search on the city in the history table
OR (@City <> '' AND @partialSearch = 1 AND (@partialSearchField = 198003 OR @partialSearchField = 199004) AND @searchAddressInHistory > 0 AND ea.EndDate IS NOT NULL AND ea.SearchableCity LIKE @searchCityLikeString))
AND((@searchAddress = '')
-- Normal search on the address
OR (@searchAddress <> '' AND (@partialSearch = 0 OR (@partialSearchField <> 198002 AND @partialSearchField <> 199003)) AND (CONTAINS(e.SearchableAddressLine1, @SearchableAddressContainsString)))
-- Partial on the address
OR (@searchAddress <> '' AND @partialSearch = 1 AND (@partialSearchField = 198002 OR @partialSearchField = 199003) AND e.SearchableAddressLine1 LIKE @searchAddressLikeString)
-- Normal search on the address in the history table
OR (@searchAddress <> '' AND (@partialSearch = 0 OR (@partialSearchField <> 198002 AND @partialSearchField <> 199003)) AND @searchAddressInHistory > 0 AND ea.EndDate IS NOT NULL AND (CONTAINS(ea.SearchableAddressLine1, @SearchableAddressContainsString)))
-- Partial search on the address in the history table
OR (@searchAddress <> '' AND @partialSearch = 1 AND (@partialSearchField = 198002 OR @partialSearchField = 199003) AND @searchAddressInHistory > 0 AND ea.EndDate IS NOT NULL AND ea.SearchableAddressLine1 LIKE @searchAddressLikeString))
AND((@phoneNumber = '')
-- Normal search on the phone number
OR (@phoneNumber <> '' AND (@partialSearch = 0 OR (@partialSearchField <> 198005 AND @partialSearchField <> 199006)) AND t.Number LIKE(@phoneNumber + '%'))
-- Partial on the phone number
OR (@phoneNumber <> '' AND @partialSearch = 1 AND (@partialSearchField = 198005 OR @partialSearchField = 199006) AND t.Number LIKE @searchPhoneNumberLikeString))
AND((@provinceID < 0)
-- Normal search on the province. If the province is other then we make the selection on the country. Otherwise this is a canadian province and we test the equality on it
OR (@provinceID = 119200 AND e.CountryID = @countryID)
OR (@provinceID > 0 AND @provinceID <> 119200 AND e.ProvinceID = @provinceID)
-- Normal search on the province in the history table. If the province is other then we make the selection on the country. Otherwise this is a canadian province and we test the equality on it
OR (@provinceID = 119200 AND @searchAddressInHistory > 0 AND ea.EndDate IS NOT NULL AND ea.CountryID = @countryID)
OR (@provinceID > 0 AND @provinceID <> 119200 AND @searchAddressInHistory > 0 AND ea.EndDate IS NOT NULL AND ea.ProvinceID = @provinceID))
-- The entity role is used has a binary value on four column in the database
AND((@entityRole = 0)
OR (@entityRole = 1 AND br.IsActiveClient = 1)
OR (@entityRole = 2 AND br.IsFormerClient = 1)
OR (@entityRole = 3 AND (br.IsActiveClient = 1 OR br.IsFormerClient = 1))
OR (@entityRole = 4 AND br.IsProfessional = 1)
OR (@entityRole = 5 AND (br.IsActiveClient = 1 OR br.IsProfessional = 1))
OR (@entityRole = 6 AND (br.IsFormerClient = 1 OR br.IsProfessional = 1))
OR (@entityRole = 7 AND (br.IsActiveClient = 1 OR br.IsFormerClient = 1 OR br.IsProfessional = 1))
OR (@entityRole = 8 AND br.HasNeverBeenClient = 1)
OR (@entityRole = 9 AND (br.IsActiveClient = 1 OR br.HasNeverBeenClient = 1))
OR (@entityRole = 10 AND (br.IsFormerClient = 1 OR br.HasNeverBeenClient = 1))
OR (@entityRole = 11 AND (br.IsActiveClient = 1 OR br.IsFormerClient = 1 OR br.HasNeverBeenClient = 1))
OR (@entityRole = 12 AND (br.IsProfessional = 1 OR br.HasNeverBeenClient = 1))
OR (@entityRole = 13 AND (br.IsActiveClient = 1 OR br.IsProfessional = 1 OR br.HasNeverBeenClient = 1))
OR (@entityRole = 14 AND (br.IsFormerClient = 1 OR br.IsProfessional = 1 OR br.HasNeverBeenClient = 1))
OR (@entityRole = 15 AND (br.IsActiveClient = 1 OR br.IsFormerClient = 1 OR br.IsProfessional = 1 OR br.HasNeverBeenClient = 1)))
-- If true, we must check only in use favorite
AND((@searchScope <> 1)
OR (@searchScope = 1 AND u.UserID = CONVERT(NVARCHAR, dbo.fn_GetUserID(@userCode))))
AND((@postalCode = '')
-- Normal search on the postal code
OR (@postalCode <> '' AND (@partialSearch = 0 OR (@partialSearchField <> 198004 AND @partialSearchField <> 199005)) AND e.PostalCode LIKE(@postalCode + '%'))
-- Partial search on the postal code
OR (@postalCode <> '' AND @partialSearch = 1 AND (@partialSearchField = 198004 OR @partialSearchField = 199005) AND e.PostalCode LIKE @searchPostalCodeLikeString)
-- Normal search on the postal code in the history table
OR (@postalCode <> '' AND (@partialSearch = 0 OR (@partialSearchField <> 198004 AND @partialSearchField <> 199005)) AND @searchAddressInHistory > 0 AND ea.EndDate IS NOT NULL AND ea.PostalCode LIKE(@postalCode + '%'))
-- Partial search on the postal code in the history table
OR (@postalCode <> '' AND @partialSearch = 1 AND (@partialSearchField = 198004 OR @partialSearchField = 199005) AND @searchAddressInHistory > 0 AND ea.EndDate IS NOT NULL AND ea.PostalCode LIKE @searchPostalCodeLikeString))
AND((@alphaCode = '')
-- Normal search on the alpha code
OR (@alphaCode <> '' AND (@partialSearch = 0 OR (@partialSearchField <> 198006 AND @partialSearchField <> 199007)) AND e.AlphaCode LIKE(@alphaCode + '%'))
-- Partial search on the alpha code
OR (@alphaCode <> '' AND @partialSearch = 1 AND (@partialSearchField = 198006 OR @partialSearchField = 199007) AND e.AlphaCode LIKE @searchAlphaCodeLikeString))
AND(IsArchived = @searchInArchived)
AND(@originatingUnitID < 0 OR (emu.MgtUnitID = @originatingUnitID AND emu.EndDate IS NULL AND emu.MgtUnitCategoryID = 193001))
AND(@administrativeUnitID < 0 OR (emu2.MgtUnitID = @administrativeUnitID AND emu2.EndDate IS NULL AND emu2.MgtUnitCategoryID <> 193001))
AND((@BDCRepresentativeMgtUnitID < 0 OR @advancedSearchType <> 0)
OR (@advancedSearchType = 0 AND rr.EndDate IS NULL AND rr.MgtUnitID = @BDCRepresentativeMgtUnitID))
AND((@BDCRepresentativeID < 0 OR @advancedSearchType <> 1)
OR (@advancedSearchType = 1 AND rr2.EndDate IS NULL AND rr2.EmployeeID = @BDCRepresentativeID))
AND((@keywordType <= 0 OR @keyword = '' OR @advancedSearchType <> 2)
-- Normal search on the keyword
OR (@keywordType > 0 AND @keyword <> '' AND @advancedSearchType = 2 AND (@partialSearch = 0 OR (@partialSearchField <> 198007 AND @partialSearchField <> 199008)) AND k.KeywordTypeID = @keywordType AND k.Descr LIKE (@keyword + '%'))
-- Partial search on the keyword
OR (@keywordType > 0 AND @keyword <> '' AND @advancedSearchType = 2 AND @partialSearch = 1 AND (@partialSearchField = 198007 OR @partialSearchField = 199008) AND k.KeywordTypeID = @keywordType AND k.Descr LIKE @searchKeywordLikeString))
AND((@entityList = '')
OR (@entityList <> '' AND e.EntityID IN (SELECT value FROM dbo.fn_split(@entityList, ','))))
--AND((@gcProjectNumber = -1 OR @advancedSearchType <> 3) OR (@advancedSearchType = 3 AND ??? = @gcProjectNumber)) --TO be implemented in delivery 5
AND ((@trueRowCount <= 200)
OR (@trueRowCount > 200 AND 1 = 0))
END
SET @retCode = @@ERROR
RETURN(@retCode)
GO
Stephen Mandeville
Database Administrator
Systems & Technology
Business Development Bank of Canada
Phone: (514) 283-8708
Fax: (514) 283-9257
e-mail:: stephen.mandeville@bdc.ca
e-mail home:: alkor@videotron.ca
Stephen Mandeville
Database Administrator
Systems & Technology
Business Development Bank of Canada
Phone: (514) 283-8708
Fax: (514) 283-9257
e-mail:: stephen.mandeville@bdc.ca
e-mail home:: alkor@videotron.ca -
Firstly, Ye Gods what a monster. Congrats on the task of working otu the logic to even get the beast running.
Secondly, is all this necessary in one procedure? Could it been broken up into a few smaller procedures that are more purpose built. You could then determine what parameters you have and call the appropriate procedure.
One thing I've found when working with SQL server is that it likes little bites. Generally, if you have something that does 10 things in one step, it'll run quicker if you do those 10 things in 10 steps. Must be something to do with it's resource usage and tracking or something.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Dead code!
((@trueRowCount <= 200)
OR (@trueRowCount > 200 AND 1 = 0))Assumes any combination(s) can be selected
Think the ORs maybe a bit of a problem optimizing wise
-
Can you post the structured code
Tried, but loose the plot a bit with the brackets.
FROM entity e
LEFT JOIN TelephoneNumber t
ON e.EntityID = t.EntityID
LEFT JOIN UserFavorite u
ON e.EntityID = u.EntityID
LEFT JOIN view_EntityBusinessRules br
ON e.EntityID = br.EntityID
LEFT JOIN EntityManagementUnit emu
ON e.EntityID = emu.EntityID
LEFT JOIN EntityManagementUnit emu2
ON e.EntityID = emu2.EntityID
LEFT JOIN RepresentativeRole rr
ON e.EntityID = rr.EntityID
LEFT JOIN RepresentativeRole rr2
ON e.EntityID = rr2.EntityID
LEFT JOIN Keyword k
ON e.EntityID = k.EntityID
LEFT JOIN EntityName en
ON e.EntityID = en.EntityID
LEFT JOIN EntityAddress ea
ON e.EntityID = ea.EntityID
LEFT JOIN BDCLookup l
ON br.EntityStatusID = l.LookupID
WHERE
(@entityType < 0
OR EntityTypeID = @entityType)
AND ((@searchCompleteName = ''
OR @entityType = @INDIVIDUAL)
-- Normal search on the name and trade name
OR ((@entityType = @BUSINESS OR @entityType < 0)
AND @searchCompleteName <> ''
AND (@partialSearch = 0 OR @partialSearchField <> 198001)
AND (CONTAINS(e.SearchableCompleteName, @SearchableNameContainsString)
OR CONTAINS(e.SearchableTradeName, @SearchableNameContainsString)))
-- Partial search on the name and trade name
OR ((@entityType = @BUSINESS OR @entityType < 0)
AND @searchCompleteName <> ''
AND @partialSearch = 1
AND @partialSearchField = 198001
AND (e.SearchableCompleteName LIKE @searchNameLikeString
OR e.SearchableTradeName LIKE @searchNameLikeString))
-- Normal search on the name and trade name in the history table
OR ((@entityType = @BUSINESS OR @entityType < 0)
AND @searchCompleteName <> ''
AND (@partialSearch = 0
OR @partialSearchField <> 198001)
AND @searchNameInHistory > 0
AND (@searchNameInNDC > 0
OR en.NameTypeID <> 196005)
AND en.EndDate IS NOT NULL
AND CONTAINS(en.SearchableName, @SearchableNameContainsString))
-- Partial search on the name and trade name in the history table
OR ((@entityType = @BUSINESS OR @entityType < 0)
AND @searchCompleteName <> ''
AND @partialSearch = 1
AND @partialSearchField = 198001
AND @searchNameInHistory > 0
AND (@searchNameInNDC > 0
OR en.NameTypeID <> 196005)
AND en.EndDate IS NOT NULL
AND en.SearchableName LIKE @searchNameLikeString))
AND ((@searchLastName = ''
OR @entityType <> @INDIVIDUAL)
-- Normal search on the last name
OR (@entityType = @INDIVIDUAL
AND @searchLastName <> ''
AND (@partialSearch = 0
OR @partialSearchField <> 199001)
AND (CONTAINS(SearchableLastName, @SearchableLastNameContainsString)))
-- Partial search on the last name
OR (@entityType = @INDIVIDUAL
AND @searchLastName <> ''
AND @partialSearch = 1
AND @partialSearchField = 199001
AND SearchableLastName LIKE @searchLastNameLikeString)
-- Normal search on the last name in the history table
OR (@entityType = @INDIVIDUAL
AND @searchLastName <> ''
AND (@partialSearch = 0
OR @partialSearchField <> 199001)
AND @searchNameInHistory > 0
AND (@searchNameInNDC > 0
OR en.NameTypeID <> 196005)
AND en.EndDate IS NOT NULL
AND CONTAINS(SearchableName, @SearchableLastNameContainsString))
-- Partial search on the last name in the history table
OR (@entityType = @INDIVIDUAL
AND @searchLastName <> ''
AND @partialSearch = 1
AND @partialSearchField = 199001
AND @searchNameInHistory > 0
AND (@searchNameInNDC > 0
OR en.NameTypeID <> 196005)
AND en.EndDate IS NOT NULL
AND en.SearchableName LIKE @searchLastNameLikeString))
AND ((@searchFirstName = ''
OR @entityType <> @INDIVIDUAL)
-- Normal search on the first name
OR (@entityType = @INDIVIDUAL
AND @searchFirstName <> ''
AND (@partialSearch = 0
OR @partialSearchField <> 199002)
AND (CONTAINS(SearchableFirstName, @SearchableFirstNameContainsString)))
-- Partial search on the first name
OR (@entityType = @INDIVIDUAL
AND @searchFirstName <> ''
AND @partialSearch = 1
AND @partialSearchField = 199002
AND SearchableFirstName LIKE @searchFirstNameLikeString)
-- Normal search on the first name in the history table
OR (@entityType = @INDIVIDUAL
AND @searchFirstName <> ''
AND (@partialSearch = 0
OR @partialSearchField <> 199002)
AND @searchNameInHistory > 0
AND (@searchNameInNDC > 0
OR en.NameTypeID <> 196005)
AND en.EndDate IS NOT NULL
AND CONTAINS(SearchableName, @SearchableFirstNameContainsString))
-- Partial search on the first name in the history table
-
Obviously it's a slow day across the Tasman.
I have to admire your dedication.
Cheers,
- Mark
Cheers,
- Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply