Last post Jun 24, 2019 07:58 AM by AddWeb Solution
Jun 20, 2019 06:01 PM|rkrex|LINK
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 !!!
Jun 21, 2019 07:19 AM|Yang Shen|LINK
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:
create procedure InsertData2
insert into Purchase values(@Date,@Prodcut,@Stock_Qty)
select * from Purchase where Date=@Date
<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" />
protected void Button1_Click(object sender, EventArgs e)
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=ddd;Integrated Security=True"))
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);
Here is result of my demo:
Jun 21, 2019 02:55 PM|rkrex|LINK
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 !!!!
Jun 24, 2019 01:44 AM|Yang Shen|LINK
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:
Jun 24, 2019 07:58 AM|AddWeb Solution|LINK
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 :