Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sp returns intermediate table value

Author  Topic 

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-02-21 : 15:35:44

The main sp(stored procedure) calls a child sp to load a value for the main sp.

In SQL management studio I see that single value of the child sp ( the intermediate value) in a table ( No Column Name), then the result set, and finally one with return Value =0

The C# application code that executes the sp returns the first table with the single intermediate value.

What can I do ?

Thanks

andrewcw

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-21 : 15:41:37
Put SET NOCOUNT ON; at the top of the parent proc.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-21 : 15:41:46
Depending on what you are using in the C# code, you will need to skip over the first result set and get the next result set. If you are using ADO.Net and DataReader, for example, you would need to use the NextResult method on the DataReader. See here: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx

Alternatively, you would need to do something in the stored proc, so the result set you want is generated first.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-21 : 15:45:07
quote:
Originally posted by russell

Put SET NOCOUNT ON; at the top of the parent proc.

Yes!! Why didn't I think of that, that is probably what it is!! I thought it was really two result sets.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-21 : 16:45:05
You still might be right. But between the two of us, I'm sure we got the OP on the right track!
Go to Top of Page

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-02-21 : 17:13:03
I put SET NO COUNT ON at start of main procededure, then added it right after the intermediate procedure & then again at end. But the behavior remains unchanged. I will look at your references at MDSN & thanks for the suggestions.

andrewcw
Go to Top of Page

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-02-21 : 17:31:48
The orignal code did this :
adapter.Fill(this.Tables[table]);

I changed it to pick up the whole data set & locate the next table
DataSet ds = new DataSet();
adapter.Fill(ds);

Sure enough the table was there, but there was NO VALUES ... the columns however were right. I then backed out all the NO COUNT ON but the 2nd table remains empty table when calling the stored procedure & filling a dataset

andrewcw
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-02-22 : 12:41:49
How is the child proc passing the values to the parent one? It can't just be a recordset. You need to use output parameters.
Go to Top of Page
   

- Advertisement -