Friday 30 January 2009

get database views based on system table - uses cursor


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

Monday 12 January 2009

MSSQL catch block to raiseerror using ErrorMessage, ErrorSeverity and ErrorState


   1:  BEGIN CATCH
   2:   
   3:  DECLARE @ErrorMessage        NVARCHAR(4000)
   4:  DECLARE @ErrorSeverity        INT
   5:  DECLARE @ErrorState            INT
   6:   
   7:  SELECT
   8:  @ErrorMessage = ERROR_MESSAGE(),
   9:  @ErrorSeverity = ERROR_SEVERITY(),
  10:  @ErrorState = ERROR_STATE();
  11:   
  12:  RAISERROR (@ErrorMessage, -- Message text.
  13:  @ErrorSeverity, -- Severity.
  14:  @ErrorState -- State.
  15:  );
  16:   
  17:  END CATCH