CREATE PROCEDURE [dbo].[sp_Auto_Calc_Rows]
	@Sazman_Code varchar(2), @Start_Date DateTime , @End_Date DateTime , 
	@stYears varchar(200)  , @Conditions char(1)  , @Grade  char(1)    ,    
	@PT_Code varchar(3) 
AS

declare @stYear  as varchar(15)

declare @stCommand    varchar(8000)
declare @stCondition  varchar(250) 

Set @stCommand = 
'Declare @Id_Havaleh Numeric(9) '+
'Declare @Dumy_Code varchar(12) '+
'Declare @iRow_Index Int '+
'Declare @stUpdate_Year varchar(20) '+
'Set @iRow_Index = 1  '+
'Declare cur_List_bime Cursor '+
'For '
While CHARINDEX(',' , @stYears) <> 0
begin
   print @stYears  
    Set  @stYear  = SubString(@stYears ,0 , CHARINDEX(',' , @stYears))
	if @Conditions = 0  
	   Set  @stCondition = ''
	else
	if @Conditions = 1  
	     Set  @stCondition  = '  And  '+@stYear+'.Grade.Status = '+ @Grade
	else
	if @Conditions = 2  
	     Set  @stCondition = '  And  '+@stYear+'.Grade.Status != '+ @Grade
	if @PT_Code <> '' 
	     Set  @stCondition = @stCondition +  '  And  '+@stYear+'.DrugH.Pt_Code = '+ @PT_Code
 
 set @stCommand = @stCommand +
	'Select '+@stYear+'.DrugH.Id_Havaleh ,'''+@stYear+''','+@stYear+'.DrugH.Dumy_Code'+
	' From '+@stYear+'.DrugH '+
	'Inner Join '+@stYear+'.Doctor   On '+@stYear+'.Doctor.Nezam_No = '+@stYear+'.DrugH.Nezam_No '+
	'Inner Join '+@stYear+'.Sahmiyeh On '+@stYear+'.Sahmiyeh.Sazman_Code = '+@stYear+'.DrugH.Sazman_Code  '+
	      'Inner Join '+@stYear+'.Grade       On '+@stYear+'.Doctor.Grade_Code          = '+@stYear+'.Grade.Grade_Code  '+	
	      'Where '+@stYear+'.DrugH.TotPriceSazman > 0 And '+@stYear+'.DrugH.Tarikh_Noskheh Between '''+Convert(Varchar(10),@Start_Date,111) +
	''' and '''+Convert(Varchar(10),@End_Date,111 ) +
	''' And '+@stYear+'.DrugH.Kind_Flag = 1 And '+@stYear+'.DrugH.Del_Flag = 1 and '+@stYear+'.DrugH.Sazman_Code='''+@Sazman_Code +'''' + @stCondition  

 Set @stYears = SubString(@stYears , Len(@stYear)+2 , Len(@stYears) - Len(@stYear)-1)
 if (LEN(@stYears) > 0 )
  Set @stCommand = @stCommand + ' UNION '
end 
 Set @stCommand = @stCommand + ' Order By Dumy_Code'
 Set @stCommand  = @stCommand  + 
								' Open cur_List_Bime  '+
								'Fetch Next From cur_List_Bime '+
								'Into @id_Havaleh , @stUpdate_Year , @Dumy_Code '+
								'While @@FETCH_STATUS = 0  '+
								'Begin '+
								  'Exec dbo.sp_UpdateRow  @stUpdate_Year, @Id_Havaleh , @iRow_Index '+
								  'Set @iRow_Index = @iRow_Index + 1 '+
								  'Fetch Next From cur_List_Bime '+
								  'Into @id_Havaleh , @stUpdate_Year , @Dumy_Code '+ 
								'End '+
								'Close cur_List_Bime '+
								'Deallocate  cur_List_Bime '+
								'print Cast(@iRow_Index-1  as varchar(4)) ' 
--print @stCommand
Execute (@stCommand)
