Tuesday, September 4, 2012

Alternate to Dynamic SQL for Search SPs


Today had a chance to work on ‘Search’ functionality using SQL Server stored procedure. Usually we write dynamic SQL in stored procedure for ‘Search’ing purpose. End user might consider few or all attributes as search criteria. To execute the SQL statement, we use sp_executesql
We have an alternate to dynamic SQL, using simple select query. Below is the sample.

Dynamic SQL Sample:
CREATE PROCEDURE [SearchOffcie]     
(  @OfficeName VARCHAR( 100 )     
 , @OfficeType VARCHAR( 50 )     
 , @Address1 VARCHAR( 100 )     
 , @Address2 VARCHAR( 100 )     
 , @City VARCHAR( 50 )     
 , @State CHAR( 2 )     
 , @OfficeTypeID INT     
 )
AS     
    
BEGIN     
SET NOCOUNT ON 

    DECLARE @SqlQry NVARCHAR( 4000 ) = ''     
    DECLARE @WhereCondition VARCHAR( 200 ) = ''     
    DECLARE @SearchCriteria VARCHAR( 200 ) = ''     
    -- Criteria     
 BEGIN      
  IF @OfficeName <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.OfficeName LIKE ' + CHAR( 39 ) + '%' + @OfficeName + '%' + CHAR( 39 )     
   END     
     
  IF @OfficeType <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.OfficeType LIKE ' + CHAR( 39 ) + '%' + @OfficeType + '%' + CHAR( 39 )     
   END     
     
  IF @Address1 <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.Address1 LIKE ' + CHAR( 39 ) + '%' + @Address1 + '%' + CHAR( 39 )     
   END     
     
  IF @Address2 <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.Address2 LIKE ' + CHAR( 39 ) + '%' + @Address2 + '%' + CHAR( 39 )     
   END     
     
  IF @City <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.City LIKE ' + CHAR( 39 ) + '%' + @City + '%' + CHAR( 39 )     
   END     
     
  IF @State <> ''     
   BEGIN     
    SET @SearchCriteria = @SearchCriteria + ' AND Office.State LIKE ' + CHAR( 39 ) + '%' + @State + '%' + CHAR( 39 )     
   END     
 
 END     
      
 -- Prepare query for dynamic execution based on the given criteria     
    SET @SqlQry = N'     
 SELECT Office.OfficeID     
       , Office.OfficeName     
       , Office.OfficeType     
         , (IsNull(Office.Address1,'''') + '', '' + IsNull(Office.Address2,'''') + '', '' +  Office.City + '', '' + Office.[State] ) [Address]
       , Office.OfficeEMail   
  FROM [MyOfficeDetails] Office WITH (NOLOCK)      
  INNER JOIN [MainOfficesRef] ORCRef WITH (NOLOCK) ON ORCREF.OfficeID = Office.OfficeID   '     
     
 -- Applying criteria
  SET @WhereCondition = ' WHERE 1 = 1 '
  SET @WhereCondition = @WhereCondition + ' AND Office.OfficeType <> 3 '     
  SET @SqlQry = @SqlQry + @WhereCondition     
     
    IF @SearchCriteria <> ''     
        BEGIN     
            SET @SqlQry = @SqlQry + @SearchCriteria     
        END     
     
    -- Execute sql query     
    EXEC sp_executesql @SqlQry     
         
    Select @@ROWCOUNT [ResultCount] 
    -- Record Count  
     
END

Sample on “Alternate to dynamic SQL using simple select”

Note:
1.       Search is of two types –
a.       Specific search – exact values should match
b.       Contains search – given value could match on the column somewhere. i.e. Name like ‘%Srinivas%’
2.       So we had an assumption that, end user will either prepend or append ‘%’ for Contains search.
 
CREATE PROCEDURE [SearchOffice1]     
(  @OfficeName VARCHAR(100)     
 , @OfficeType VARCHAR(50)     
 , @Address1 VARCHAR(100)     
 , @Address2 VARCHAR(100)     
 , @City VARCHAR(50)     
 , @State CHAR(2)     
 , @OfficeTypeID INT     
 , @MaxRecordCountToReturn INT = 50
 , @MessageToUser NVARCHAR(MAX) = '' OUTPUT )     
AS  
BEGIN     
  
 DECLARE @Spacer Varchar(1)= SPACE(0) 
 DECLARE @ResultSetCount BIGINT =
 SET NOCOUNT ON 
 
  SELECT TOP (@MaxRecordCountToReturn) 
      O.[OfficeID] 
     ,O.[OfficeTypeID] 
     ,O.[OfficeName] 
     ,ISNULL(O.[OfficeType],@Spacer) [OfficeType] 
     ,(IsNull(O.Address1,'') + ', ' + IsNull(O.Address2,'') + ', ' +  O.City + ', ' +  
     O.[State]) [Address]       
     ,ISNULL(O.[OfficeEMail],@Spacer) [OfficeEMail] 
     ,OST.OfficeStatusTypeDesc 
  FROM  
    [Office] O WITH (NOLOCK) 
    INNER JOIN [OfficeRegionCrossRef] ORCR WITH (NOLOCK) ON O.OfficeID=ORCR.OfficeID
     
    AND (O.[OfficeName] LIKE @OfficeName OR @OfficeName IS NULL) 
    AND (O.[OfficeType] LIKE @OfficeType OR @OfficeType IS NULL) 
    AND (O.[Address1] LIKE @Address1 OR @Address1 IS NULL) 
    AND (O.[Address2] LIKE @Address2 OR @Address2 IS NULL) 
    AND (O.[City] LIKE @City OR @City IS NULL) 
    AND (O.[State] LIKE @State OR @State IS NULL) 
     
  ORDER BY O.DateCreated DESC
   
  SET @ResultSetCount = @@ROWCOUNT 
   
  IF (@MaxRecordCountToReturn>0) -- A VALUE OF 0 INDICATES RETURN ALL ROWS 
    IF (@ResultSetCount>@MaxRecordCountToReturn) 
     SET @MessageToUser = 'Attention: Search results exceed the maximum defined threshold. Please refine your search criteria' 
     --RAISERROR ('Error: Search results exceed the maximum defined threshold. Please refine your search criteria', 16, 1 ) 
END

-- Thanks to my collegue.

No comments: