Running a SQL code before running a Stored procedure

Question

I have a SQL Data Source defined as follows

<asp:SqlDataSource ID="SqlDatajob" runat="server" 
    ConnectionString="<%$ ConnectionStrings:prod_oshk %>" 
    SelectCommand="kirsd_JobDetailSP" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="TxtSearch" Name="SearchJob" PropertyName="Text" 
            Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

I would like to run a different stored procedure in the same session before running kirsd_JobDetailSP. The other stored procedure sets up a environment for running kirsd_JobDetailSP.
I dont want to modify kirsd_JobDetailSP .
Is it possible?

Answer 1

Yes, you can switch the SelectCommandType to Text and use plain code, e.g.

execute FirstProcedure;
execute kirsd_JobDetailSP @searchJob = @searchJob;

-------- Then both procedures will execute. Although it is not clear what exactly do you mean by setting environment.

Answer 2

Hi，@Shashi Bhosale
Welcome to Microsoft T-SQL Q&A Forum!

According to your description, it looks more like a problem of ASP calling stored procedures.
In Tsql, the execution order of stored procedures can be called by creating processes. You can try to see if asp can call stored procedures asynchronously.
The following is the code :

protected void Page_Load(object sender, EventArgs e)  
{  
    SqlConnection cnn1 = new SqlConnection("Your connection string");  
    SqlConnection cnn2 = new SqlConnection("Your connection string");  
    SqlCommand cmd1;  
    SqlCommand cmd2;  
    IAsyncResult result1;  
    IAsyncResult result2;  
    SqlDataReader reader1;  
    SqlDataReader reader2;  
   
    try  
    {  
        cnn1.Open();  
   
        cmd1 = new SqlCommand("SP1", cnn1);  
        cmd1.CommandType = System.Data.CommandType.StoredProcedure;  
        result1 = cmd1.BeginExecuteReader(CommandBehavior.SingleRow);  
   
        cnn2.Open();  
        cmd2 = new SqlCommand("SP2", cnn2);  
        cmd2.CommandType = System.Data.CommandType.StoredProcedure;  
        result2 = cmd2.BeginExecuteReader(CommandBehavior.SingleRow);  
   
        reader1 = cmd1.EndExecuteReader(result1);  
   
        if (reader1.Read())  
        {  
            Literal1.Text = reader1[0].ToString();  
        }  
   
        reader1.Close();  
   
        reader2 = cmd2.EndExecuteReader(result2);  
   
        if (reader2.Read())  
        {  
            Literal2.Text = reader2[0].ToString();  
        }  
   
        reader2.Close();  
    }  
    catch (Exception ex)  
    {  
        // raise an exception or do whatever logic you want  
    }  
    finally  
    {  
        if (cnn1.State != System.Data.ConnectionState.Closed)  
            cnn1.Close();  
   
        if (cnn2.State != System.Data.ConnectionState.Closed)  
            cnn2.Close();  
    }  
}  

If you want to learn about asynchronous calls, check out this article.And If this code doesn't work, please change your tags and ask the ASP engineers.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.