Click here to Skip to main content
Rate this: bad
good
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)
AS
BEGIN
    print @a
    print 'test'
 
  if @a=1 begin
    exec test @a
  end
END
(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?
 
thanks
Fabio
Posted 22-Nov-12 0:35am
v2
Rate this: bad
good
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;
  Permalink  
Comments
Fabio Ottavi at 22-Nov-12 6:59am
   
same comment as Solution 2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

hi there
you can do this :
sqlcommand.commantype=commandtype.storeProcedure;
sqlcommand.parameter(new sqlparamete("your storeprocedure parameter"));
dqldatareader dr=sqlcommand.executeDataReader();
your solution will be same this.
  Permalink  
Comments
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
good
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?
  Permalink  
Comments
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;
spcmd.Parameters.Add(theOrderID);
scn.Open();
dr = spcmd.ExecuteReader();
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hi
 

 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);
           adapter.Fill(dTable);
 

                return dTable;
            }
 
  Permalink  
v2

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 598
1 OriginalGriff 280
2 George Jonsson 258
3 CPallini 190
4 Animesh Datta 130
0 OriginalGriff 5,985
1 Sergey Alexandrovich Kryukov 5,341
2 CPallini 4,760
3 George Jonsson 3,400
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 22 Nov 2012
Copyright © CodeProject, 1999-2014
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