Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005C#, +
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
Edited 22-Nov-12 0:40am
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
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 545
1 OriginalGriff 498
2 sanket saxena 330
3 Abhinav S 280
4 thatraja 275
0 Sergey Alexandrovich Kryukov 8,372
1 OriginalGriff 4,830
2 Peter Leow 3,784
3 Maciej Los 3,515
4 Er. Puneet Goel 3,107


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 22 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid