SQL SERVER Check if variable is null and then assign statement for Where Clause

Question

I am trying to achieve something like the below in WHERE clause in sql    if   zipCode   null  begin   Portal   dbo   Address  Position Filter  radiusBuff    1     end else if  zipCode   null  begin   Portal   dbo   Address  PostalCode  zipCode   end     I tried the following   WHERE    zipCode IS NOT NULL AND   Portal   dbo   Address  PostalCode  zipCode   OR   zipCode IS NULL AND   Portal   dbo   Address  Position Filter  radiusBuff    1      which is wrong  Can anyone help in framing the exact statement  Thanks

User · Answer

Try the following   if   select VisitCount from PageImage where PID  pid and PageNumber 5  is NULL  begin     update PageImage     set VisitCount 1     where PID  pid and PageNumber  pageno end  else begin     update PageImage      set VisitCount VisitCount 1     where PID  pid and PageNumber  pageno end

User · Answer

is null is the syntax I use for such things  when COALESCE is of no help   Try   if   zipCode is null    begin       Portal   dbo   Address  Position Filter  radiusBuff    1       end else    begin       Portal   dbo   Address  PostalCode  zipCode     end

User · Answer

Isnull   syntax is built in for this kind of thing   declare  Int int   null   declare  Values table   id int  def varchar 8     insert into  Values values  8   I am 8     -- fails select   from  Values where id    Int  -- works fine select   from  Values where id   isnull  Int  8     For your example keep in mind you can change scope to be yet another where predicate off of a different variable for complex boolean logic   Only caveat is you need to cast it differently if you need to examine for a different data type   So if I add another row but wish to specify int of 8 AND also the reference of text similar to  repeat  I can do that with a reference again back to the  isnull  of the first variable yet return an entirely different result data type for a different reference to a different field   declare  Int int   null   declare  Values table   id int  def varchar 16     insert into  Values values  8   I am 8     8   I am 8 repeat     select   from  Values where id   isnull  Int  8  and def like isnull cast  Int as varchar     repeat

User · Answer

Try a case statement  WHERE CASE WHEN  zipCode IS NULL THEN 1 ELSE  zipCode END

User · Answer

is null can be used to check whether null data is coming from a query as in following example    declare  Mem varchar 20   flag int select  mem MemberClub from  dbo   UserMaster  where UserID  uid if  Mem is null  begin     set  flag  0  end else begin     set  flag 1  end return  flag

[sql] SQL SERVER: Check if variable is null and then assign statement for Where Clause

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to case