Calling Stored Procedure in SQL Server from C# only executes part of the Stored Procedure

[Maury French]

Good day all.

I have a Stored Procedure in MS SQL Server Version 17.9.1. In a nutshell, it checks for a table and drops it if it exists, recreates it, then inserts data into it based on queries. When I run it within SQL Server, it works fine and does everything. However, when I call the Stored procedure from C#, it will drop and re-create the table, but never go into portion of the Stored Procedure that inserts values based on they query portion of the Stored Procedure, and returns a value of 0.

Here are the commands from C#. The Stored procedures is cumbersome and somewhat proprietary so sharing it would be difficult. Even then, shouldn't the call from C# to the Stored Procedure execute all the Stored Procedure? Is there a limit on the number of drop/create/inserts that can be done from one C# program?

Thanks in advance for any advice you have as I am confused as to why this doesn't work.


SqlCommand cmd = new SqlCommand(commandname);
cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandTimeout = 0;
cmd.Parameters.Add(new SqlParameter("@report_year", value: rpt_year));
cmd.Parameters.Add(new SqlParameter("@Quarter", value: Quarter));
cmd.Parameters.Add(new SqlParameter("@College", value: College));
cmd.Connection = sqlConnection1;
int returnval = cmd.ExecuteNonQuery();
sqlConnection1.Close();
Close();

Continue reading...