Dynamic Query

 

Dynamic SQL is needed when we need to retrieve a set of records based on different search parameters. Say for example - An employee search screen or a general purpose report which needs to execute a different SELECT statement based on a different WHERE clause.

NOTE: Most importantly, the Dynamic SQL Queries in a variable are not compiled, parsed, checked for errors until they are executed.

Execute

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)

Sp_execute_sql

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

 

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

 

Difference between execute and sp_execute_sql

sp_execute_sql 

·        Allows for statements to be parameterized.

Creates a plan on first execution (similar to stored procedures) and subsequent executions reuse this plan

Execute

SQL Server can create a plan for each execution.