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 = 0 
 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.

Comments

Post a Comment

Popular posts from this blog

ASP.NET Compillation Error BC31007 - Solution

-
TToday while I am working on the ASP.NET application, I suddenly encountered with below error while trying to debug the application. I tried couple of actions: .    ·           Clean and rebuild solution ·          IDE restart ·          Temporary asp.net files cleanup ·          System restart ·          Modified Identity in App pool Finally it got resolved by modifying the Identity to “Local System” in Application Pool Server Error in '/My App’ Application. Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC31007 : Unable to open module file 'C:\Windows\Microsoft.NET\Framework64\v4...
Read more

Microsoft Surface in brief

-
After years of covert development, Microsoft says it will release a computer that uses the tabletop as its high-resolution display, recognizes objects placed on the surface and skips the traditional keyboard and mouse in favor of fingers on the screen. What it is : A computer in the form of a table, using the hard acrylic tabletop as a high-resolution screen. First product from Microsoft's previously secret Surface Computing team, which has 120 employees. How it works : The surface itself isn't touch-sensitive, but a series of cameras inside the table can see when someone places or drags a finger, hand or any other object on or across the tabletop screen. Internal projector lights screen from beneath. Interface : People can use their hands to touch and move virtual objects on the screen, just as they would with a mouse on a traditional PC. The system also can recognize objects placed on the surface, based on their shape or on special codes affixed to them.Size: 22 inches high, ...
Read more

Test & Debug WCF service using WCFTestClient.exe

-
Image
VS.NET 2010 provides WCFTestClient.exe for us to test and debug the WCF service.  Please follow me to test and debug the service. Generally, first debug and then test the service, but here first I will tell you how to test it and then how to debug it J Test the WCF service using WCFTestClient. 1.        Open “Visual Studio Command Prompt (2010)” 2.        Go to “c:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\” 3.        Run WcfTestClient.exe 4.        It will open WCF Test Client, now add WCF Service (URL) 5.        Select the service method, from left panel 6.        Provide request data (i.e. values to parameters) on right panel and click on Invoke button. It gives you the response. Debug the WCF service, follow the commands. 1.     ...
Read more