Running a SQL code before running a Stored procedure

Shashi Bhosale 1 Reputation point

I have a SQL Data Source defined as follows

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

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?

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Naomi 7,366 Reputation points

    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.

  2. Bert Zhou-msft 3,421 Reputation points

    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;  
            cmd1 = new SqlCommand("SP1", cnn1);  
            cmd1.CommandType = System.Data.CommandType.StoredProcedure;  
            result1 = cmd1.BeginExecuteReader(CommandBehavior.SingleRow);  
            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();  
            reader2 = cmd2.EndExecuteReader(result2);  
            if (reader2.Read())  
                Literal2.Text = reader2[0].ToString();  
        catch (Exception ex)  
            // raise an exception or do whatever logic you want  
            if (cnn1.State != System.Data.ConnectionState.Closed)  
            if (cnn2.State != System.Data.ConnectionState.Closed)  

    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.

    0 comments No comments