Question

Bien, la duda es la siguiente:

Tenemos una función en PHP que hace varias consultas a la base de datos entonces como el trabajo es principalmente en base de datos queremos crear un procedimiento para no estar llamando al servidor de base de datos, luego al servidor donde está PHP y así varias veces. El código de la función es el siguiente:

function ValidateSections($SectionID = 0, $MasterSectionID = 0)
{
    global $conn;
    // Total Number of Questions for the PropForm
    $question_sql = "SELECT fk_QuestionID, Question_IncludedBy, Question_Required, fk_QuestionID_Parent, tblPropForm_Sections.fk_SectionID, 'S' + cast(tblPropForm_Sections.fk_SectionID as varchar) + '_P' + cast(fk_QuestionID_Parent as varchar) + '_Q' + cast(fk_QuestionID as varchar) as QuestionName
      FROM tblSection_Questions, tblPropForm_Sections
      where tblSection_Questions.fk_SectionID = tblPropForm_Sections.fk_SectionID
       and fk_PropFormID = ".$_SESSION['propform'][1];

    if ($SectionID != 0)
    {
        $question_sql = "SELECT fk_QuestionID, Question_IncludedBy, Question_Required, fk_QuestionID_Parent, tblPropForm_Sections.fk_SectionID, 'S' + cast(tblPropForm_Sections.fk_SectionID as varchar) + '_P' + cast(fk_QuestionID_Parent as varchar) + '_Q' + cast(fk_QuestionID as varchar) as QuestionName
      FROM tblSection_Questions, tblPropForm_Sections
      where tblSection_Questions.fk_SectionID = tblPropForm_Sections.fk_SectionID
       and fk_PropFormID = ".$_SESSION['propform'][1]." AND tblSection_Questions.fk_SectionID = ".$SectionID;
    }
    if ($MasterSectionID != 0)
    {
        $question_sql = "SELECT fk_QuestionID, Question_IncludedBy, Question_Required, fk_QuestionID_Parent, tblPropForm_Sections.fk_SectionID, 'S' + cast(tblPropForm_Sections.fk_SectionID as varchar) + '_P' + cast(fk_QuestionID_Parent as varchar) + '_Q' + cast(fk_QuestionID as varchar) as QuestionName
      FROM tblSection_Questions, tblPropForm_Sections
      where tblSection_Questions.fk_SectionID = tblPropForm_Sections.fk_SectionID
       and fk_PropFormID = ".$_SESSION['propform'][1]." AND tblPropForm_Sections.fk_MasterSectionID = ".$MasterSectionID;
    }

    $stmt = sqlsrv_query( $conn, $question_sql );
    if($stmt === false) die( print_r( sqlsrv_errors(), true) );

    $numofquestions = 0;
    $numrequired = 0;
    while( $question_row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_NUMERIC) ) 
    {   
        // 1. Is the Question Required?

        if ($question_row[2] == 1)
        {       
            $TriggerParent = findTriggerParent($question_row[5],$question_row[3]);              
            $TriggerQuestion = checkIncludeBy($question_row[1]);

            if (($TriggerQuestion) && ($TriggerParent))
                {
                    $numofquestions++;
                    //echo $question_row[5].'<br/>Parent Needed:'.($TriggerParent ? 'true' : 'false').' - Triggered:'.($TriggerQuestion ? 'true' : 'false').'<br/>';

                    if (!checkUserAnswered($question_row[5]))
                    {
                        $numrequired = $numrequired+1;
                    }
            }
        }   
    }
    return array($numofquestions,$numrequired);
}

Y el trabajo que yo había hecho en la base de datos SQL Server es el siguiente:

proc [dbo].[propform_proc1]
    @SectionID varchar(100),
    @MasterSectionID varchar(100),
    @SessionID int,
    @qIncludeBy varchar(100),
    @numofquestions as int output,
    @numrequired as int output
as 
select fk_questionID,Question_IncludedBy, Question_Required,fk_QuestionID_Parent,tblPropForm_Sections.fk_SectionID,
'S' + cast(tblPropForm_Sections.fk_SectionID as varchar) + 'P' + cast(fk_QuestionID_Parent as varchar) + 
'_Q' + cast(fk_QuestionID as varchar) as QuestionName
FROM tblSection_Questions, tblPropForm_Sections
where tblSection_Questions.fk_SectionID = tblPropForm_Sections.fk_SectionID
and fk_PropFormID = @SessionID

if @SectionID != 0
BEGIN
    SELECT fk_QuestionID, Question_IncludedBy, Question_Required, fk_QuestionID_Parent, 
    tblPropForm_Sections.fk_SectionID, 'S' + cast(tblPropForm_Sections.fk_SectionID as varchar) + 
    '_P' + cast(fk_QuestionID_Parent as varchar) + '_Q' + cast(fk_QuestionID as varchar) as QuestionName
    FROM tblSection_Questions, tblPropForm_Sections
    where tblSection_Questions.fk_SectionID = tblPropForm_Sections.fk_SectionID
    and fk_PropFormID = @SessionID AND tblSection_Questions.fk_SectionID = @SectionID
END
if @MasterSectionID != 0
BEGIN
    SELECT fk_QuestionID, Question_IncludedBy, Question_Required, fk_QuestionID_Parent, tblPropForm_Sections.fk_SectionID, 
    'S' + cast(tblPropForm_Sections.fk_SectionID as varchar) + '_P' + cast(fk_QuestionID_Parent as varchar) + '_Q' + 
    cast(fk_QuestionID as varchar) as QuestionName
    FROM tblSection_Questions, tblPropForm_Sections
    where tblSection_Questions.fk_SectionID = tblPropForm_Sections.fk_SectionID
    and fk_PropFormID = @SessionID AND tblPropForm_Sections.fk_MasterSectionID = @MasterSectionID
END
set @numofquestions = 0
set @numrequired = 0
set @qIncludeBy = (select Question_IncludedBy 
FROM tblSection_Questions,tblPropForm_Sections 
WHERE tblSection_Questions.fk_SectionID = tblPropForm_Sections.fk_SectionID 
AND fk_PropFormID = @SessionID AND tblPropForm_Sections.fk_MasterSectionID = @MasterSectionID)

Return @numofquestions
Return @numrequired

Bien, llegado a este punto lo que yo pienso es que como la función PHP a su vez llama a más funciones, la solución que intentamos implementar no va a ser muy efectiva porque realmente no va a liberarnos mucho de trabajo los servidores. Qué opináis?

Answer 1

Dale un vistazo a esto PHP, Stored Procedures and SQL Server, ve si te sirve