How to Dynamically Add to the TSQL Where Clause in a Stored Procedure

How to dynamically add conditions to the TSQL Where clause?

I'm trying to avoid selecting between two complete Select statements. The following is very similar, but less complicated, than my actual code.

SELECT COUNT(*)
FROM   MyTable
WHERE  ShipDate >= @FirstDayOfMonth
开发者_如何学JAVAAND    ShipDate <  @LastDayOfMonth
AND    OrderType = 1
AND    NOT OrderCode LIKE '%3'
AND    NOT OrderCode LIKE '%4';

I would like to be able to add or remove the last two conditions depending on the value of a stored procedure parameter.

i.e.: if @ExcludePhoneOrders = False , don't use the last two Where clause conditions.

I'm not sure if you meant to include or exclude, but here is a stab at it.

SELECT COUNT(*)
FROM   MyTable
WHERE  ShipDate >= @FirstDayOfMonth
AND    ShipDate <  @LastDayOfMonth
AND    OrderType = 1
AND    (@ExcludePhoneOrders = False OR (NOT OrderCode LIKE '%3' AND NOT OrderCode LIKE '%4'));

It should look something like this... If the datatype I used is not right, you should change them. But the main concept should remain the same. Let me know how it goes.

Create proc GetOrders
   ....,
   @FirstDayOfMonth int,
   @LastDayOfMonth int,
   ....
AS
DECLARE @SQL varchar(1000)
DECLARE @PARAMS nvarchar(100);

set @SQL='SELECT COUNT(*) FROM   MyTable WHERE  ShipDate >= @FirstDay_OfMonth AND    ShipDate <  @LastDay_OfMonth AND    OrderType = 1';

if(@ExcludePhoneOrders = 1)
begin
  set @SQL=@SQL + ' AND NOT OrderCode LIKE ''%3'' AND NOT OrderCode LIKE ''%4'''
end

SET @PARAMS = '@FirstDayOfMonth int, @LastDayOfMonth int'
EXECUTE sp_executesql @SQL, @PARAMS, @FirstDay_OfMonth = @FirstDayOfMonth, @LastDay_OfMonth=@LastDayOfMonth
GO

You could dump the query to a varchar, then use an if statement to optionally append the extra where clause info, then pass the whole thing to sp_executesql.

SELECT COUNT(*)
FROM   MyTable
WHERE  ShipDate >= @FirstDayOfMonth
AND    ShipDate <  @LastDayOfMonth
AND    OrderType = 1
AND (
    (@ExcludePhoneOrders <> False AND NOT OrderCode LIKE '%3' AND NOT OrderCode LIKE '%4')
    OR
    @ExcludePhoneOrders = False 
)

You need consider handle NULL value if parameter can be NULL