SUJOY SANTRA

A dynamically build Transact-SQL statements can be executed using EXECUTE Command or sp_executesql statement. Here, in this article in my examples, I'll be using sp_executesql which is more efficient, faster in execution and also supports parameter substitution. If we are using EXECUTE command to execute the SQL String, then all the parameters should be converted to character and made as a part of the Query before execution. But the sp_executesql statement provides a better way of implementing this. It allows us to substitute the parameter values for any parameter specified in the SQL String. Before getting into the actual example, let me differentiate these two commands with a simple example. Say - selecting a record from the employee table using the ID in the WHERE clause.
The basic syntax for using EXECUTE command:

EXECUTE(@SQLStatement)

The basic syntax for using sp_executesql:

sp_executesql [@SQLStatement],[@ParameterDefinitionList],
[@ParameterValueList]

Example 1.0

/* Using EXECUTE Command */
/* Build and Execute a Transact-SQL String with a single parameter 
 value Using EXECUTE Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String with parameter value */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = ' + 
CAST(@EmpID AS NVARCHAR(10))
/* Execute Transact-SQL String */
EXECUTE(@SQLQuery)

Example 1.1

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

Example 2.0

Let us take a simple example - Employee Table with common fields such as EmployeeID, Name, Department, Designation, JoiningDate, Salary and Description. You can use the following Transact-SQL CREATE TABLE statement to create a Employee Table within your database.

/* Transact-Sql to create the table tblEmployees */
CREATE TABLE tblEmployees
(
    EmployeeID       SMALLINT IDENTITY(1001,1) NOT NULL,
    EmployeeName     NVARCHAR(100) NOT NULL,
    Department       NVARCHAR(50) NOT NULL,
    Designation      NVARCHAR(50) NOT NULL,
    JoiningDate      DATETIME NOT NULL,
    Salary           DECIMAL(10,2) NOT NULL,
    [Description]    NVARCHAR(1000) NULL 
)

/* Transact SQL to insert some sample records into tblEmployee table */
INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Smith', 'IT Research', 'Research Analyst', 
 '02/08/2005', 23000.00, 'Analyst since 2005')

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('John Micheal', 'IT Operations', 'Manager', 
 '07/15/2007', 15000.00, NULL)

INSERT INTO tblEmployees
(EmployeeName, Department, Designation, 
 JoiningDate, Salary, [Description]) 
VALUES    
('Will Smith', 'IT Support', 'Manager', 
 '05/20/2006', 13000.00, 'Joined last year as IT Support Manager')

/* This stored procedure builds dynamic SQL and executes 
using sp_executesql */
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

Example 3.0 - Using LIKE Operator

  /* 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

/* 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

 /* 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