Dynamic query in sql server

Question

CREATE PROC [dbo].[GetSimExpiryDate]
   (@val varchar(5)) 
AS
BEGIN
    IF(@val ='1')
    BEGIN
       (SELECT 
            WS.WSRecordId, WS.SIMNumber,
            BES.DisplayName, 
            BES.PIN, BES.BESSqlServer,
            CONVERT(VARCHAR(11), WS.ExpiryDate, 106) as ExpiryDate
        FROM   
            WEBSERVICE_CACHE AS WS   
        LEFT OUTER JOIN  
            BES_SERVER_CACHE AS BES ON WS.SIMNumber = LEFT(BES.ICCID, 19)
        WHERE
            CONVERT(DATETIME, GETDATE(), 109) > CONVERT(DATETIME, WS.ExpiryDate, 109))
   END
   ELSE IF(@val ='2')
   BEGIN
      (SELECT  
           WS.WSRecordId, WS.SIMNumber,
           BES.DisplayName, 
           BES.PIN, BES.BESSqlServer,
           CONVERT(VARCHAR(11), WS.ExpiryDate, 106) as ExpiryDate    
       FROM   
           WEBSERVICE_CACHE AS WS   
       LEFT OUTER JOIN  
           BES_SERVER_CACHE AS BES ON WS.SIMNumber = LEFT(BES.ICCID,19)  
       WHERE 
           MONTH( WS.ExpiryDate) = MONTH(GETDATE()) 
           AND YEAR(WS.ExpiryDate) = YEAR(GETDATE())
           AND CONVERT(DATETIME, GETDATE(), 109) <= CONVERT(DATETIME, WS.ExpiryDate, 109))
END

I have a stored procedure which has if and else blocks based on the parameter passed in procedure

In both the queries all the code is same except the where clause how to remove this redundancy. As I have lot of elseif

Anik Islam Abhi · Accepted Answer · 2014-11-13 07:04:33Z

2

yes you can make it dynamic . you have to use sp_Executesql

Example

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

For more read here

answered Nov 13, 2014 at 7:04

Anik Islam Abhi

25.4k8 gold badges61 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Poonam Singhania Over a year ago

Anik, I am calling the stored proc from the data acess layer .cs file then in this Execute sp_Executesql is not required I guess

Anik Islam Abhi Over a year ago

sp_Executesql will be calling inside your store procedure. you don't have to worry about . sp_Executesql is way to execute a string as sql query .

Poonam Singhania Over a year ago

If I have only one parameter then whats the use for paramdefinition

Anik Islam Abhi Over a year ago

then just execute the query . paramdefinition is optional . i have added it's documentation link where you can learn more about it.

Collectives™ on Stack Overflow

Dynamic query in sql server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related