0
@pSUPPLIER_ID NVARCHAR(500)
@pSUPPLIER_NAME NVARCHAR(500)

Based on the availability of value in above variable, I need to swich fileds in WHERE clause. In single instance only one above variable contains value.

WHERE SUPPLIER.NAME LIKE @pSUPPLIER_NAME

Or

WHERE SUPPLIER.ID = @pSUPPLIER_ID

I have done this in two separate SQLs with a 'IF'.

IF(ISNULL(@PSUPPLIER_ID, 0) <> 0)
    SELECT * FROM SUPPLIER WHERE SUPPLIER.ID = @pSUPPLIER_ID
ELSE
    SELECT * FROM SUPPLIER WHERE SUPPLIER.NAME LIKE @pSUPPLIER_NAME

I want to know whether is there a possibility to have this in a single SQL 2005. This is in a SP at a given time only one input variable is passed(based on what we need to search). Simple 'OR' won't work I guess

Buddhi Dananjaya
  • 643
  • 2
  • 12
  • 32
  • 2
    Why don't you literally use an `OR` in your WHERE clause? – Tab Alleman Jul 19 '16 at 18:27
  • Because, at a given time I only check for one value. If I use OR it will check for NULL in the other (not supplied one) – Buddhi Dananjaya Jul 19 '16 at 18:30
  • 1
    The result will be the same, since NULL != NULL, the parameter that contains NULL will not contribute to the results. However, if performance is the concern, there is no single query that will perform as fast as your current 2-query solution. – Tab Alleman Jul 19 '16 at 18:33
  • 1
    A shame, since my answer would have explained this. `SET` the variable beforehand. Check the values of those variables in the order you wish it to exist. THEN RUN THE STATEMENT. This way you use LITERALS in your predicate and help `SQL Server` to use the most efficient use of a `SARG` statement and `INDEXES`. – clifton_h Jul 19 '16 at 18:38
  • 1
    WHERE (SUPPLIER.NAME LIKE at_pSUPPLIER_NAME OR at_pSUPPLIER IS NULL) AND (SUPPLIER.ID = at_pSUPPLIER_ID OR at_pSUPPLIER IS NULL) – PacoDePaco Jul 19 '16 at 19:02

0 Answers0