0

I have an sql query that I am wanting to run and want to add something to the where clause if I mark a parameter as true. I didn't think I would need to have the same sql statement twice, but can't find a way to do this. This is what I want. 

DECLARE @getShipped VARCHAR = 'false'; 

SELECT DISTINCT 
    Serial_No
INTO #Serials
FROM Part_v_Container_Change2 AS CC
WHERE Change_Date <= @dateEnding
   *** AND IF @getShipped = 'true' THEN CC.Container_Status = 'Shipped' ***

Have tried if statements and case statements but can't seem to get this to work? I just don't want to repeat sql if I don't have too.

Danrex
  • 1,657
  • 4
  • 31
  • 44
  • Possible duplicate of [Optional Arguments in WHERE Clause](http://stackoverflow.com/questions/10185638/optional-arguments-in-where-clause) – Tab Alleman Jan 11 '16 at 21:10
  • This is not a duplicate. If you read what you suggest they do not cover anything in regards to inserting an extra where clause based on a boolean value set by yourself. I think you have may not have read my question properly and jumped the gun. – Danrex Jan 12 '16 at 00:10

2 Answers2

7

Try it like this

DECLARE @getShipped VARCHAR(20) = 'false'; 

SELECT DISTINCT 
     Serial_No
INTO #Serials
FROM Part_v_Container_Change2 AS CC
WHERE Change_Date <= @dateEnding AND
   ((@getShipped = 'true' AND CC.Container_Status = 'Shipped') OR @getShipped = 'false')
Mihail Stancescu
  • 4,088
  • 1
  • 16
  • 21
  • 2
    Missing an `AND` after the first line in the WHERE clause – AHiggins Jan 11 '16 at 20:48
  • I might not be explaining myself properly. It is returning null when I run this. I am wanting to add the 'CC.Container_Status = shipped' to the where clause if @getShipped is true which I will add to the sql myself. – Danrex Jan 11 '16 at 23:09
  • 1
    The code I have posted does what you need but you have a problem in your code posted. You need to declare `@getShipped VARCHAR(20)` not `VARCHAR` because that means that the variable will be `VARCHAR(1)` and `@getShipped` will never be equal with `'true'` and `'false'`. I've edited my code to and now it works as intended. – Mihail Stancescu Jan 12 '16 at 08:06
0

Try dynamic sql something like below 

DECLARE @getShipped VARCHAR(20) = 'false'; 
DECLARE @sql NVARCHAR(255) = 'SELECT DISTINCT Serial_No INTO #Serials FROM Part_v_Container_Change2 AS CC
WHERE Change_Date <= ''' + + CONVERT(VARCHAR, @dateEnding, 101) + ''''

DECLARE @filter NVARCHAR(50) = ' AND CC.Container_Status = ''Shipped'''

IF @getShipped = 'true' 
    SET @sql = @sql + @filter

EXEC (@sql)
techspider
  • 3,370
  • 13
  • 37
  • 61
  • This looks like what I need but it's returning the error - Could not find stored procedure 'S'. – Danrex Jan 11 '16 at 23:10
  • [Bad habits to kick : declaring VARCHAR without (length)](https://sqlblog.org/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length) – Damien_The_Unbeliever Jan 12 '16 at 08:11
  • I have edited to declare size of VARCHAR; can you try to print out @sql and see what is there? My code is random and you might require to concatenate values properly – techspider Jan 12 '16 at 17:24