Query times out only when null value is entered

• transgress

  SSC Enthusiast

  Points: 191

  More actions

  July 16, 2003 at 11:59 am

  #80531

  Hi. This query only times out when @religion is null... Can anyone help?

  DECLARE @Religion varchar(50)

  --SET @Religion='L070'

  SET @Religion=NULL

  select top 1000 u.userID,birthdate,replyrate,lessthan11,photo,DATEDIFF(YEAR, birthdate, GETDATE() ) - CASE WHEN birthdate > GETDATE() THEN 1 ELSE 0 END

  AS age from userinfo u,userdetails ud

  where ud.userid = u.userID and ud.religion = ( CASE WHEN @Religion IS NULL OR @Religion = '' THEN ud.religion END ) OR CHARINDEX (',' + CAST(ud.religion AS varchar) + ',', ',' + @Religion + ',' ) > 0

  Thanks

  Anne

  
  Anne

• guarddata

  SSCertifiable

  Points: 6583

  More actions

  July 16, 2003 at 12:06 pm

  #465714

  Just a thought. Try putting an ELSE condition in your CASE (i.e. CASE WHEN @Religion IS NULL OR @Religion = '' THEN ud.religion ELSE @Religion END )

  You also need to remember that adding a NULL in a string produces NULL. The CHARINDEX probably needs to replace @Religion with ISNULL( @Religion,'')

  Guarddata-

• transgress

  SSC Enthusiast

  Points: 191

  More actions

  July 16, 2003 at 12:18 pm

  #465721

  thanks everyone... I found the solution. if I pass an empty string instead of the null value, it gives a return...

  Anne

  
  Anne