locked
Debugging Stored Procedures RRS feed

  • Question

  • User-305496339 posted

    Hi Friends,

    I was wondering are there some techniques I could implement towards debugging my stored procs. I want to debug them in Visual studio as well as the sql management studio. I was just wondering is there a way I can see my table variables as I assign data to them? I just need to be able to isolate things and see what is happening instead of running everything all at one time. Thanks !!!

    Thursday, June 20, 2019 6:01 PM

Answers

  • User-719153870 posted

    Hi rkrex,

    We can debug stored procedures in vs just like debugging C# code.

    Please follow the next steps to debug the stored procedure in vs, so that you can get the parameters you submit in the stored procedure (See your table variables as you assign data to them):

    First, you need to include your database in your vs project. Press Ctrl+\+s in VS to open ‘SQL Server Object Explorer’.

    Right-click on 'SQL Server', and select' Add SQL Server...'.

    Enter'Server Name', select'Database Name', and click'Connect'.

    At this point, you can see your new database in SQL Server Object Explorer, expand Databases folder-your database-Programmability-Stored Procedures in turn, and you can see your stored procedure.

    Double-click to open the stored procedure you want to debug. You can set break points in the stored procedure, just like when you debug your C# code.

    Then, right-click your database in the ‘SQL Server Object Explorer’ and select 'Application Debugging'.

    Then, in ‘Solution Explorer’, right-click Properties under the project you want to debug and select 'Open'.

    Chose the ‘SQL Server’ under ‘Debuggers’ under the ‘Web’ option of ‘Properties’.

    At this point, you can debug stored procedures just like debugging C # code.

    Please refer to my demo codes:

    ASPX:

    Stored Procedures:

    create procedure InsertData2
    @Date varchar(50),
    @Prodcut varchar(50),
    @Stock_Qty int
    as
    insert into Purchase values(@Date,@Prodcut,@Stock_Qty)
    select * from Purchase where Date=@Date

    ASPX:

    <div>
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="Add" OnClick="Button1_Click" />
            </div>

    CS:

    protected void Button1_Click(object sender, EventArgs e)
            {
                using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ddd;Integrated Security=True"))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("InsertData2",conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Date", SqlDbType.VarChar, 50).Value = TextBox1.Text;
                    cmd.Parameters.Add("@Prodcut", SqlDbType.VarChar, 50).Value = TextBox2.Text;
                    cmd.Parameters.Add("@Stock_Qty", SqlDbType.Int).Value = Convert.ToInt32( TextBox3.Text);
                    cmd.ExecuteNonQuery();
                }
            }

    Here is result of my demo:

    Best Regards,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 21, 2019 7:19 AM
  • User-719153870 posted

    Hi rkrex,

    There's a simpler way to debug, and you can see your result set.

    Right-click your stored procedure in 'SQL Server Object Explorer', select 'Debug Procedure...', and then add debugging data.

    Press F10 to start the step-by-step debugging of the stored procedure. After debugging, you can see the debugging results below.

    Please refer to below GIF:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 24, 2019 1:44 AM
  • User-1038772411 posted

    Hello, rkrex

    Yes, there are some techniques to debugg stored procedures in c# and already some people gives you examples. now i m providing you one refrence file for Debugging stored procedure in c#.

    Microsoft Documentation Link : 

    https://docs.microsoft.com/en-us/aspnet/web-forms/overview/data-access/advanced-data-access-scenarios/debugging-stored-procedures-cs

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 24, 2019 7:58 AM

All replies

  • User-719153870 posted

    Hi rkrex,

    We can debug stored procedures in vs just like debugging C# code.

    Please follow the next steps to debug the stored procedure in vs, so that you can get the parameters you submit in the stored procedure (See your table variables as you assign data to them):

    First, you need to include your database in your vs project. Press Ctrl+\+s in VS to open ‘SQL Server Object Explorer’.

    Right-click on 'SQL Server', and select' Add SQL Server...'.

    Enter'Server Name', select'Database Name', and click'Connect'.

    At this point, you can see your new database in SQL Server Object Explorer, expand Databases folder-your database-Programmability-Stored Procedures in turn, and you can see your stored procedure.

    Double-click to open the stored procedure you want to debug. You can set break points in the stored procedure, just like when you debug your C# code.

    Then, right-click your database in the ‘SQL Server Object Explorer’ and select 'Application Debugging'.

    Then, in ‘Solution Explorer’, right-click Properties under the project you want to debug and select 'Open'.

    Chose the ‘SQL Server’ under ‘Debuggers’ under the ‘Web’ option of ‘Properties’.

    At this point, you can debug stored procedures just like debugging C # code.

    Please refer to my demo codes:

    ASPX:

    Stored Procedures:

    create procedure InsertData2
    @Date varchar(50),
    @Prodcut varchar(50),
    @Stock_Qty int
    as
    insert into Purchase values(@Date,@Prodcut,@Stock_Qty)
    select * from Purchase where Date=@Date

    ASPX:

    <div>
                <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="Add" OnClick="Button1_Click" />
            </div>

    CS:

    protected void Button1_Click(object sender, EventArgs e)
            {
                using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ddd;Integrated Security=True"))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("InsertData2",conn);
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Parameters.Add("@Date", SqlDbType.VarChar, 50).Value = TextBox1.Text;
                    cmd.Parameters.Add("@Prodcut", SqlDbType.VarChar, 50).Value = TextBox2.Text;
                    cmd.Parameters.Add("@Stock_Qty", SqlDbType.Int).Value = Convert.ToInt32( TextBox3.Text);
                    cmd.ExecuteNonQuery();
                }
            }

    Here is result of my demo:

    Best Regards,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 21, 2019 7:19 AM
  • User-305496339 posted

    Thank You So Much for going the extra mile with this  comprehensive example. It really helps me a lot. Just out of curiosity I was wondering how could i include animation with my  How-to examples with my posts. I just have one more question; How would I view my resultset , for example the select statement that you used... Select * from Purchase where date =@date. One thing I noticed is that my debug window closes after it gets to a line where it is running a select statement. Thanks So Kindly !!!! 

    Friday, June 21, 2019 2:55 PM
  • User-719153870 posted

    Hi rkrex,

    There's a simpler way to debug, and you can see your result set.

    Right-click your stored procedure in 'SQL Server Object Explorer', select 'Debug Procedure...', and then add debugging data.

    Press F10 to start the step-by-step debugging of the stored procedure. After debugging, you can see the debugging results below.

    Please refer to below GIF:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 24, 2019 1:44 AM
  • User-1038772411 posted

    Hello, rkrex

    Yes, there are some techniques to debugg stored procedures in c# and already some people gives you examples. now i m providing you one refrence file for Debugging stored procedure in c#.

    Microsoft Documentation Link : 

    https://docs.microsoft.com/en-us/aspnet/web-forms/overview/data-access/advanced-data-access-scenarios/debugging-stored-procedures-cs

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 24, 2019 7:58 AM