nextresult

[frazer]

i want to execute 2 stored procs and get teh result using nextresult.
this gives me an error

sqlCommand.CommandText ="spProc1; spProc2";

is that possible to execute 2 stored procs or can we just execute 2 or more
sql statements
and use nextresult on it?

thnx

 

[Branimir Giurov]

2 statements inside one stored proc will work.
You can embed the two proc call in a third proc - this way you'll get the
result from both of them with one call to ExecuteReader.

The reason for two procs not working at once, is becouse of the fact that
when you execute the stored proc, ADO.NET passes a "exec [procname] [@params
list]" to SQL. So when you call "sp_proc1; sp_proc2" or something like this,
it sends "exec sp_proc1; proc2" which is not a valid command according to
SQL

There is another option, of you still want to execute the procs from the
code, and in a batch.
It will be a little slower, but will work. Here is the solution:

Use the following command text: "exec spProc1; exec spProc2". Then DON'T SET
the CommandType to stored proc.

Cheers,
Branimir