title: how to perform sorting and filtering in stored procedure with performance optimization?
body: <p>I want to perform sorting and filtering in my stored procedure.</p>
  
  <p>My create table for Holiday table:</p>
  
  <pre><code>CREATE TABLE [dbo].[Holiday](
      [HolidaysId] [int] IDENTITY(1,1) NOT NULL,
      [HolidayDate] [date] NULL,
      [HolidayDiscription] [nvarchar](500) NULL,
      [Name] [nvarchar](max) NULL,
   CONSTRAINT [PK_Holiday] PRIMARY KEY CLUSTERED 
  (
      [HolidaysId] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <p>My filtering criteria would be as:</p>
  
  <ol>
  <li>Starts With</li>
  <li>Is Equal to</li>
  <li>Not Equal to.</li>
  </ol>
  
  <p><strong>Note:Please ignore HolidayId in filter comparision.</strong></p>
  
  <p>My Table:Holiday</p>
  
  <pre><code>HolidaysId int,Name nvarchar(500),HolidayDate date.
  </code></pre>
  
  <p>Sample Input:</p>
  
  <pre><code>HolidayId       Name       Date
   1               abc       1/1/2015
   2               pqr       1/2/2015
   3               xyz       1/3/2015
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>Case 1:Starts with(This is just for name column only.likewise i want to do for HolidayDate column too)
  Input:ab(filtering parameter)
  Query:where Name like '%ab%' order by Name(when sort column name is  passed as parameter in stored procedure for column to sort(for eg:Name))
  output:1,abc,1/1/2015
  
  Case 2:Is Equal to(Same as above)
  Input:prr(filtering parameter)
  output:2,pqr,1/2/2015
  
  Case 3:Not Equal to(Same as above)
  Input:bbb(filtering parameter)
  output:All Records
  </code></pre>
  
  <p>This is my stored procedure so far:</p>
  
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [dbo].[sp_PagedItems]
      (
       @Page int,
       @RecsPerPage int
      )
  AS
  
  -- We don't want to return the # of rows inserted
  -- into our temporary table, so turn NOCOUNT ON
  SET NOCOUNT ON
  
  
  --Create a temporary table
  CREATE TABLE #TempItems
  (
      ID int,
      Name varchar(50),
      HolidayDate date
  )
  
  
  -- Insert the rows from tblItems into the temp. table
  INSERT INTO #TempItems (ID, Name,HolidayDate)
  SELECT HolidaysId,HolidayDiscription,HolidayDate FROM holiday 
  
  -- Find out the first and last record we want
  DECLARE @FirstRec int, @LastRec int
  SELECT @FirstRec = (@Page - 1) * @RecsPerPage
  SELECT @LastRec = (@Page * @RecsPerPage + 1)
  
  -- Now, return the set of paged records, plus, an indiciation of we
  -- have more records or not!
  SELECT *,
         MoreRecords = 
      (
       SELECT COUNT(*) 
       FROM #TempItems TI
       WHERE TI.ID &gt;= @LastRec
      ) 
  FROM #TempItems
  WHERE ID &gt; @FirstRec AND ID &lt; @LastRec
  
  
  -- Turn NOCOUNT back OFF
  SET NOCOUNT OFF
  </code></pre>
  
  <p>Now there are 4 things i would send to my stored procedure are:</p>
  
  <ol>
  <li>Page no</li>
  <li>PageSize(number of records to retrive)</li>
  <li>Sorting Column Name(Name Or HolidayDate)</li>
  <li>My filter Column name(Name of Holidaydate) and Operator like StartWith or Equal to or not equal to.(ColumnName and Operator)</li>
  </ol>
  
  <p>Can anybody help me to perform sorting and filtering and if any performance optimization related changes is there then please please do suggest me.</p>