Thursday 23 April 2009

rounding errors between SQL and .NET - why helps to use decimal type instead of float

I came across a situation where when the database datatype is float, and we read the data into a .NET datatable, the automatic conversion is from float to double. For some reason when this happens there are occasionally cases of rounding errors. The way to overcome this (other than changing the float columns to decimal(18, 6) or some other decimal spec, is to access the float data using some CONVERT(decimal(18, 6) col_name_here) so that the .NET data table has decimal type in the column in question.

Tuesday 21 April 2009

cursor to fetch database views


   1:  declare @fetchStatus as int;
   2:  set @fetchStatus = 0
   3:  declare @str as varchar(100), @views as varchar(100)
   4:  declare tmp cursor for
   5:  select    [name] from    sys.views
   6:  open tmp
   7:   
   8:  while @fetchStatus = 0 begin   
   9:  fetch tmp into @views   
  10:  set @fetchStatus = @@fetch_status   
  11:  if @fetchStatus = 0 begin       
  12:  print @views       
  13:  set @str = 'select * from ' + @views + ' where 0 = 1'       
  14:  exec (@str)   
  15:  end
  16:  end
  17:   
  18:  close tmp
  19:  deallocate tmp