CREATE PROCEDURE [dbo].[spSortPrescriptionList]
    @Sazman_Code VARCHAR(300),
    @Start_Date DATETIME,
    @End_Date DATETIME,
    @stYears VARCHAR(200),
    @Conditions CHAR(1),
    @Grade CHAR(1),
    @Nezam_No NVARCHAR(10),
    @PT_Code VARCHAR(3),
    @OrderFlag CHAR(1)
AS
DECLARE @cmd AS VARCHAR(MAX);
DECLARE @con AS VARCHAR(MAX);
DECLARE @Order AS VARCHAR(100);

IF @OrderFlag = 1
    SET @Order = ' Tarikh_Noskheh , Dumy_Code ';
ELSE IF @OrderFlag = 2
    SET @Order = ' DrugH.Pt_Code , DrugH.Nezam_No , DrugH.Tarikh_Noskheh ,Dumy_Code ';
ELSE IF @OrderFlag = 5
    SET @Order = ' DrugH.Tarikh_Noskheh , DrugH.Nezam_No ';
ELSE IF @OrderFlag = 6
    SET @Order = ' DrugH.Pt_Code , DrugH.Nezam_No , Dumy_Code , DrugH.Tarikh_Noskheh  ';
ELSE
    SET @Order = ' Meli_Code ,DrugH.Nezam_No , DrugH.Tarikh_Noskheh ,Dumy_Code  ';

IF @Conditions = 0
    SET @con = '';
ELSE IF @Conditions = 1
    SET @con = '  And  Grade.Status = ' + @Grade;
ELSE IF @Conditions = 2
    SET @con = '  And  Grade.Status != ' + @Grade;
ELSE IF @Conditions = 3
    SET @con = '  And  DrugH.Nezam_No = ''' + @Nezam_No + '''';

IF @PT_Code <> ''
    SET @con = @con + '  And  DrugH.Pt_Code = ' + @PT_Code;
SET @cmd
    = 'UPDATE DrugH SET Radif_Noskheh = DH.RowNumber ' + 'FROM DrugH ' + 'JOIN ( '
      + 'Select DrugH.Id_Havaleh , ROW_NUMBER() OVER (ORDER BY ' + @Order + ') AS RowNumber ' 
	  + 'From DrugH '
      + 'Inner Join Doctor   On Doctor.Nezam_No  =DrugH.Nezam_No '
      + 'Inner Join Sahmiyeh On Sahmiyeh.Sazman_Code = DrugH.Sazman_Code '
      + 'Inner Join Grade    On DrugH.Grade_Code = Grade.Grade_Code ' + 'Where DrugH.TotPriceSazman > 0 And '
      + 'DrugH.Tarikh_Noskheh Between ''' + CONVERT(VARCHAR(10), @Start_Date, 111) + ''' And '''
      + CONVERT(VARCHAR(10), @End_Date, 111) + ''' And ' + 'DrugH.Kind_Flag = 1 And ' + 
	  'DrugH.Del_Flag = 1 And '
      + 'DrugH.Sazman_Code in(' + @Sazman_Code + ')' + @con + ') AS DH ' + 'ON DH.Id_Havaleh = DrugH.Id_Havaleh; ';

EXECUTE (@cmd);
SET @cmd = '';