Question: I have a stored procedure which has a parameter `@FilterToUse`.

I have a query inside the stored procedure as shown below:

```
INSERT INTO #StatusCalculation
    SELECT COUNT(*) 
    FROM Data  
    WHERE DataType = 'Bid' 
      --This part can change based on SP param       
      AND CreatedDate BETWEEN CONVERT(DATETIME, ('1/1' + '/' + @RequestYear))  
                          AND CONVERT(DATETIME, ('12/31' + '/' + @RequestYear))
      --end dynamic part
      AND Services LIKE '%Streamline Payroll%' 
      AND Services LIKE '%GlobalView Payroll%'

```

So if I have a different filter I ll have to write

```
 INSERT INTO #StatusCalculation
    SELECT COUNT(*) 
    FROM Data  
    WHERE DataType = 'Bid' 
      --This part can change based on param       
      AND BidId BETWEEN 1 AND 10
      --end dynamic part
      AND Services LIKE '%Streamline Payroll%' 
      AND Services LIKE '%GlobalView Payroll%'

```

Now my issue is that based on the `@FilterToUse` parameter I might have to use a different query expression. The same query based on different options is used about 10 times. Is there a way that I can dynamically add the filter conditions to the query based on parameter without duplicating the whole query. 

I was thinking if somehow I can do the below

```
declare @data nvarchar(max)
Set @data = 'and CreatedDate between convert(datetime,("1/1"+"/"+@RequestYear))  
    and convert(datetime,("12/31"+"/"+@RequestYear))'

```

and then just add that variable to the query

```
Insert into #BidStatusCalculation
    select 'SL Payroll',Count(*) from DashboardData
    where DataType = 'Bid'
    @data

```

Answer: 
You need to use dinamic SQL for this purpose. 
First define and assign the @DinamicPart but you may include this in the call to the SP and consider as a parameter.

```
DECLARE @DinamicPart AS NVARCHAR(MAX), -- This is the part that will be passed as parameter to the SP
        @SQL         AS NVARCHAR(MAX);
SET @DinamicPart = ' AND CreatedDate BETWEEN CONVERT(DATETIME, (''1/1/' + @RequestYear+'))  
                          AND CONVERT(DATETIME, (''12/31/' + @RequestYear))';
SET @SQL = 'INSERT INTO #StatusCalculation
    SELECT COUNT(*) 
    FROM Data  
    WHERE DataType = 'Bid' 
      --This part can change based on SP param       ' +
     @DinamicPart + '     
      --end dynamic part
      AND Services LIKE ''%Streamline Payroll%'' 
      AND Services LIKE ''%GlobalView Payroll%'';
-- This part if for debugging purposes as Dinamic SQL can be very tricky
PRINT @SQL;
-- And last part, you run the Dinamic SQL
EXEC SP_ExecuteSQL @SQL;

```
Proposed solution:

```
insert into #StatusCalculation
select COUNT(*)
from Data
where DataType = 'Bid'
    --This part can change based on param       
    and 1 = (
    case @FilterToUse
        when 1 then
            case when year(CreateDate) = @RequestYear then 1 else 0 end
        when 2 then
            case when BidId between 1 and 10 then 1 else 0 end
    end
    )
    --end dynamic part
    and Services like '%Streamline Payroll%'
    and Services like '%GlobalView Payroll%'

```