Select count(*) returns a null value

• ceapinto

  Old Hand

  Points: 378

  More actions

  November 5, 2003 at 5:50 am

  #83401

  Hi,

  I have a procedure to calculate the number of work days in a period of time (don't count saturdays and sundays).

  In SQL Server 6.5 it worked fine, but now in SQL 2000 it is not working. The problem is that when select count(*) returns a 0 (Zero), the if changes this value to null, and then compare null with > 0. I tried to use SET ANSI_NULLS on and off, but without success. The server was migrated from 6.5, and the compatibility level is set to 6.5.

  What worries me is that this situation could be happening on other procedures. I can easily replace this procedure, but I rather to discover what's happening.

  Any suggestions ?

  set nocount on

  declare @i int

  set @i = 0

  while @i < 10

  begin

  --print @i

  if ( select datepart(weekday,getdate()+@i) where datepart(weekday,getdate()+@i) not in (1,7) ) > 0 -- is not null

  begin

  print 'a'

  end

  else

  begin

  print 'b'

  end

  set @i = @i + 1

  end

• Antares686

  SSC Guru

  Points: 125444

  More actions

  November 5, 2003 at 7:53 am

  #480904

  Duplicate post please see / post in http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=17916&FORUM_ID=8&CAT_ID=1&Topic_Title=Select+count%28%2A%29+returns+a+null+value&Forum_Title=T%2DSQL