Dynamic Query in sql with like, in and orderby

Create Procedure sp_EmployeeSelect
    /* Input Parameters */
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary    Decimal(10,2)
        
AS
    Set NoCount ON
    /* Variable Declaration */
    Declare @SQLQuery AS NVarchar(4000)
    Declare @ParamDefinition AS NVarchar(2000) 
    /* Build the Transact-SQL String with the input parameters */ 
    Set @SQLQuery = 'Select * From tblEmployees where (1=1) ' 
    /* check for the condition and build the WHERE clause accordingly */
    If @EmployeeName Is Not Null 
         Set @SQLQuery = @SQLQuery + ' And (EmployeeName = @EmployeeName)'

    If @Department Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Department = @Department)' 
  
    If @Designation Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Designation = @Designation)'
  
    If @Salary Is Not Null
         Set @SQLQuery = @SQLQuery + ' And (Salary >= @Salary)'

    If (@StartDate Is Not Null) AND (@EndDate Is Not Null)
         Set @SQLQuery = @SQLQuery + ' And (JoiningDate 
         BETWEEN @StartDate AND @EndDate)'
    /* Specify Parameter Format for all input parameters included 
     in the stmt */
    Set @ParamDefinition =      ' @EmployeeName NVarchar(100),
                @Department NVarchar(50),
                @Designation NVarchar(50),
                @StartDate DateTime,
                @EndDate DateTime,
                @Salary    Decimal(10,2)'
    /* Execute the Transact-SQL String with all parameter value's 
       Using sp_executesql Command */
    Execute sp_Executesql     @SQLQuery, 
                @ParamDefinition, 
                @EmployeeName, 
                @Department, 
                @Designation, 
                @StartDate, 
                @EndDate,
                @Salary
                
    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

This sample stored procedure takes few parameter's as input and uses two variables to build and execute. @SQLQuerywhich is used to build the dynamic SQL-statement. @ParamDefinition which is used to define the Parameter's format. Whiling building the SQL string in each step, an IF-statement is used to check whether that inputted parameter is Null or not. If it is not NULL, then that parameter will be included in the SQL statement which basically adds a condition in the WHERE clause of the SQL statement. You can clearly see in the procedure that the variable@ParamDefinition contains all the parameter lists and finally sp_Executesql takes SQL-query, parameter list and the parameter values to executes a SELECT statement.

Let us consider some of the criteria listed above and see how this stored procedure works.

1. Search for specific Employee Detail with the name.
   
   

   /* 1. Search for specific Employee Detail with the Name say 'John Smith'. */
   EXEC sp_EmployeeSelect 'John Smith', NULL, NULL, NULL, NULL, NULL

   Executing the above statement will list the details of the Employee "John Smith".
2. List of Employees in a specific Department, AND
3. List of Employees in a specific Designation.
   
   

   /* 2. List of Employees in a specific Department. AND 
   3. List of Employees in a specific Designation. */
   /* Say Department = 'IT Operations'  AND  Designation = 'Manager'*/
   EXEC sp_EmployeeSelect NULL, 'IT Operations', 'Manager', NULL, NULL, NULL

   Executing the above statement will list the Details of Managers in the IT Operations Department.

Using Like Operator, IN Operator and Order By In Dynamic SQL

when we are building Dynamic SQL, there may be some instances where we need to use LIKE operator, IN operator and Order BY Clause. But the parameters used with these operators and Order By Clause doesn't work the way as they normally do for "=" and "Between" operator while using sp_executesql. Generally sp_executesql doesn't do a parameter substitution for order by clause and doing so causes a column-referencing problem. Straightly UsingLIKE operator and IN operator causes syntax error, which cannot be rectified when we are including the parameter into the Dynamic SQL statement. This problem can be resolved by including the actual parameter value in the Dynamic SQL statement. Below are the examples that show how to use Like Operator, IN Operator and OrderByclause while using sp_executesql.

Example 3.0 - Using LIKE Operator

Example 3.0 uses LIKE operator to select the list of Employees with the Name 'John'. Here in this example, the parameter is not included in the SQL statement, instead the actual value of the parameter is added to the SQL statement. So here, there is no need of parameter definition for executing the SQL string. The same applies to the other two examples shown below:

/* Variable Declaration */
DECLARE @EmpName AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @EmpName = 'John' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeName LIKE '''+ '%' + @EmpName + '%' + '''' 
EXECUTE sp_executesql @SQLQuery

Example 3.1 - Using IN Operator

Example 3.1 uses IN operator to select the Employee details ( ID = 1001, 1003 ):

/* Variable Declaration */
DECLARE @EmpID AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single 
parameter value Using sp_executesql Command */
SET @EmpID = '1001,1003' 
SET @SQLQuery = 'SELECT * FROM tblEmployees 
WHERE EmployeeID IN(' + @EmpID + ')'
EXECUTE sp_executesql @SQLQuery

Example 3.2 - Using Order By Clause

Example 3.2 sorts the Employee records by "Department" column.

/* Variable Declaration */
DECLARE @OrderBy AS NVARCHAR(50)
DECLARE @SQLQuery AS NVARCHAR(500)

/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */
SET @OrderBy = 'Department' 
SET @SQLQuery = 'SELECT * FROM tblEmployees Order By ' + @OrderBy

EXECUTE sp_executesql @SQLQuery