Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL-server-2005 C# , +
I have a strange problem calling a stored procedure from c# code.
This is the scenario:
I have a DB browser application with which I run different kinds of queries on a SQL Server DB (eg. Select, Update, execute stored procedure...)
So in my app I have an edit window where i write the T-SQL text that I run with this code:
IDbCommand command = connection.CreateCommand();
command.Connection = connection;
command.CommandTimeout = connection.CommandTimeout;
command.CommandType = CommandType.Text;
command.CommandText = textBoxQuery.Text
IDbReader reader = command.ExecuteReader();
Quite strightforward.
And I have a stored procedure with a structure like:
CREATE PROCEDURE dbo.test (@a as int)
    print @a
    print 'test'
  if @a=1 begin
    exec test @a
(obviously this is just the prototype of a real SP that does something)
A simple test procedure that should fail with maximum nesting exception with @a=1 and just print something with any other value.
I try to run the following query:
exec test 0
I get an SqlException for maximum nesting level reached.
Actually I get this exception for any value I pass to the SP! To be sure that the value is passed correctly, I commented out the recursion part, and the correct value is printed.
If I run the same query in Management Studio it runs correctly with any value, and fails only with @a=1
I don't have any clue on what is happening... any advice?
Posted 22-Nov-12 1:35am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Try Changing followings as shown below
command.CommandType = CommandType.StoredProcedure;
command.CommandText = 'test @a=' + textBoxQuery.Text;
Fabio Ottavi at 22-Nov-12 6:59am
same comment as Solution 2
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

hi there
you can do this :
sqlcommand.parameter(new sqlparamete("your storeprocedure parameter"));
dqldatareader dr=sqlcommand.executeDataReader();
your solution will be same this.
Fabio Ottavi at 22-Nov-12 6:59am
Yes, I tried to change the query type to commandtype.storeProcedure, and it actually works. The problem is that I don't know in advance if the text represents a select/update query or a stored procedure. And also passing parameters is much more painful if I have to parse them from the query text and add them as SqlParameters.
The "exec" approach seemed fine and simple, and, first of all, is a correct T-SQL statement, but I can't get it to work.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

You are doing recursion which is not going to stop anywhere.
When you give 1 as input to sp it is continuously called till error comes. So
You should change you logic.Why you are using recursion?
Fabio Ottavi at 22-Nov-12 7:29am
Obviously this is not a real function, it's just a test. In my real SP I need a "controlled" recursion.
But this test should run fine if I pass any value but 1.
pradiprenushe at 22-Nov-12 7:49am
Recursion is happening when input is 1. So same sp is getting called infinite times
i.e. nesting id happeing infinite times. I think nesting upto 32 is allowed in sql.
If you are having probleb in sp use then try this code
SqlConnection scn = new SqlConnection(connection);
SqlCommand spcmd = new SqlCommand("test" , scn);
spcmd.CommandType = CommandType.StoredProcedure
SqlParameter spara= new SqlParameter("@a", SqlDbType.Int);
spara= 0; // set value here
SqlDataReader dr;
dr = spcmd.ExecuteReader();
Rate this: bad
Please Sign up or sign in to vote.

Solution 4


 using (SqlConnection conn = new SqlConnection(ConnString))
           SqlCommand cmd = new SqlCommand("procGetAllEmployeesNoXeception", conn);
           cmd.CommandType = CommandType.StoredProcedure;

           DataTable dTable = new DataTable("dTable");
           SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                return dTable;

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Zoltán Zörgő 220
1 Peter Leow 160
2 CPallini 120
3 CHill60 100
4 BillWoodruff 95
0 Sergey Alexandrovich Kryukov 9,428
1 OriginalGriff 6,901
2 Peter Leow 4,737
3 Zoltán Zörgő 4,349
4 CHill60 2,932

Advertise | Privacy | Mobile
Web03 | 2.8.150129.1 | Last Updated 22 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100