Running a SQL code before running a Stored procedure

Shashi Bhosale 1 Reputation point
2022-03-25T15:29:22.46+00:00

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?

Transact-SQL
Transact-SQL
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
    2022-03-25T15:38:13.673+00:00

    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
    2022-03-28T01:53:14.193+00:00

    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.

    0 comments No comments