Click here to Skip to main content
13,046,533 members (69,431 online)
Rate this:
Please Sign up or sign in to vote.
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 0: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 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 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 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 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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 22 Nov 2012
Copyright © CodeProject, 1999-2017
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