ALTER procedure [dbo].[usp_swap_buffer] 
     @buffer_name    sysname, 
     @offline_name    sysname,
     @prod_id        smallint, 
     @debug            bit = 0 as
     begin
         set nocount on;
         declare @sql    nvarchar(512),
                 @status int;

				 declare @offline_count int;
 
         -- Swap previous buffer with fresh buffer
         if exists (select * from sys.objects where object_id = object_id(@offline_name) and objectProperty(object_id, N'IsUserTable') = 1)
		 and @buffer_name != 'casb_incomingbuffer'
		 
		 begin

		 if( @buffer_name != 'casb_incomingbuffer')
		 
             begin
                 
                 select @offline_count = dbo.udf_table_rowcount(@offline_name);
                 if @offline_count > 0
                     begin
                         set @sql = N'usp_swap_buffer(): Saved offline buffer content ' + @offline_name;
                         exec dbo.usp_log N'error', @sql, @offline_name, @prod_id, @debug;
                     end
 
                 if exists (select * from sys.objects where object_id = object_id(@offline_name) and objectProperty(object_id, N'IsUserTable') = 1)
                     begin
                         set @sql = N'drop table ' + @offline_name;
                         exec @status = dbo.usp_run_sql @SQL, N'usp_swap_buffer()', @debug;
                         if @status <> 0
                             begin
                                 return -1;
                             end
                     end
             end
 
         begin try
             begin transaction;
                 set @sql = N'select top 0 * into dbo.' + @offline_name + N'_tmp from dbo.' + @buffer_name + ';';
                 exec dbo.usp_run_sql @sql, N'usp_swap_buffer', @debug;
             commit;
			 
			 begin transaction;
                 set @sql = N'
                 exec sp_rename ''' + @buffer_name + N''', ''' + @offline_name + N''', @objtype = ''Object'''; 
                 exec dbo.usp_run_sql @sql, N'usp_swap_buffer', @debug;
             commit;
             begin transaction;
			 set @sql = N'
                  exec sp_rename ''' + @offline_name + N'_tmp'', ''' + @buffer_name + N''', @objtype = ''Object''';
                 exec dbo.usp_run_sql @sql, N'usp_swap_buffer', @debug;
             commit;
         end try
         begin catch
             rollback;
             exec dbo.usp_error_log_info N'usp_swap_buffer()', N'failed to perform buffer swapping.', @debug;
             RAISERROR(N'usp_swap_buffer() failed to perform buffer swapping', 16, 1);
         end catch		
		return 0;        
		end

		 else if exists (select * from sys.objects where object_id = object_id(@offline_name) and objectProperty(object_id, N'IsUserTable') = 1)
		 and @buffer_name= 'casb_incomingbuffer'
		 begin           
                  
                 select @offline_count = dbo.udf_table_rowcount(@offline_name);
                 if @offline_count > 0
                     begin
                         set @sql = N'usp_swap_buffer(): Saved offline buffer content ' + @offline_name;
                         exec dbo.usp_log N'error', @sql, @offline_name, @prod_id, @debug;
                     end
 
                 if exists (select * from sys.objects where object_id = object_id(@offline_name) and objectProperty(object_id, N'IsUserTable') = 1)
                     begin
                         set @sql = N'drop table ' + @offline_name;
                         exec @status = dbo.usp_run_sql @SQL, N'usp_swap_buffer()', @debug;
                         if @status <> 0
                             begin
                                 return -1;
                             end
                     end          
 
         begin try
             begin transaction;
                 set @sql = N'select  * into dbo.' + @offline_name + N' from dbo.' + @buffer_name + '(nolock) ;';
                 exec dbo.usp_run_sql @sql, N'usp_swap_buffer', @debug;
				 print @sql;
             commit;
			 
			 begin transaction;
                  set @sql = N'delete from dbo.' + @buffer_name + ';';
                 exec dbo.usp_run_sql @sql, N'usp_swap_buffer', @debug;
				 print @sql;
             commit;
             
         end try
         begin catch
             rollback;
             exec dbo.usp_error_log_info N'usp_swap_buffer()', N'failed to perform buffer swapping.', @debug;
             RAISERROR(N'usp_swap_buffer() failed to perform buffer swapping', 16, 1);
         end catch
		 return 0;
		 end
 
     end  -- usp_swap_buffer
 
 