Multiple SQL statements in a stored procedure

Hi!

I got 2 stored procedure, proc1 executes proc2,
proc2 does some updates and inserts on different tables ...

proc1:

ALTER PROCEDURE
AS
execute proc2

SELECT * FROM tblFoo
______________________
my problem is, that when executing proc1, I receive the message:

"THE SP executed successfully, but did not return records!"

But I need the resultset from "SELECT * FROM tblFoo" that is executed
at the end of proc1.
I'm not sure, but I think that I solved a similira problem with "set
nocount on", I put it into both SP, but it's still the same ... no
resultset ...

How can I display "SELECT * FROM tblFoo" within a SP, where SQL
statements are executed before?!

Thank you!Err, stupid question but did you check that tblFoo actually has records in
it?

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1117053332.823938.254990@.g14g2000cwa.googlegr oups.com...
> Hi!
> I got 2 stored procedure, proc1 executes proc2,
> proc2 does some updates and inserts on different tables ...
>
> proc1:
> ALTER PROCEDURE
> AS
> execute proc2
> SELECT * FROM tblFoo
> ______________________
> my problem is, that when executing proc1, I receive the message:
> "THE SP executed successfully, but did not return records!"
> But I need the resultset from "SELECT * FROM tblFoo" that is executed
> at the end of proc1.
> I'm not sure, but I think that I solved a similira problem with "set
> nocount on", I put it into both SP, but it's still the same ... no
> resultset ...
> How can I display "SELECT * FROM tblFoo" within a SP, where SQL
> statements are executed before?!
> Thank you!

Multiple SP values overriding the desired SP value

I have a stored procedure (lets call him Earl to keep things clear) that executes another sp (lets call him Daryl). Daryl returns a value, and although the last thing the first Earl does is selects a record set to return to the code, the only thing the code can access is values from Daryl. I am using NOCOUNT ON in the beginning of Earl, and NOCOUNT OFF at the end of Earl, but I still get Daryl's values.

I even put NOCOUNT ON / OFF in Daryl, but no such luck. How do I bypass Daryl's values and get to Earl's?

Thanks.

Could you post your code?|||

NOCOUNT only effects the rowcount (as in the message (n row(s) affected), and the global @.@.rowcount) not the actual returning of values.

So, are you saying the rowcount is being updated even with SET NOCOUNT ON ?

|||

no, nothing to do with the row count I guess.

What it is: because the secondary SP, which is called by the first, uses a select statement, that data generated from the select is being returned from the primary SP, and not the intended data.

Here is a boiled down version of the SP:

alter procedure earl
as
begin

insert into table (x, y, z) values ('a', 1, 'c')
@.id = @.@.identity

exec daryl(@.id )

select * from table where id = @.id

end

alter daryl
@.id int
as
begin
insert into joiningtable (x, y, z) values (@.id, 1, 'c')

@.error = @.@.error

if(@.error > 0)
select @.error as result
else
select @.@.identity as result

end

so the output from Earl is @.@.identity from Daryl, and not "select * from table where id = @.id " from Earl.

Daryl needs to return data, because this SP can be called directly from another function if @.id is known.

|||Earl will return the intended records in the second result set. So you can try to reach the Next resultset.|||

Yes, this will return two result sets.

What are you using on the client side to recieve these results? If try this from sqlcmd\osql\SSMS, you will see both result sets.

Multiple set result from a stored procedure as the data source

My stored procedure generates two results:
1) declares a variable executes a view to obtain date driven data which has
a begin and end
2) selects the data associated with date driven info
When RS executes the stored procedure I only get the first set results (the
date information).
Can RS handle multiple results from a stored procedure? If so is there any
information to read.
I can change my stored procedure with a couple joins but I am curious how RS
works (I'm a newbee).
SincerelyRS only works with the first resultset.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
(the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
RS
> works (I'm a newbee).
> Sincerely|||Sorry...RS doesn't support multiple result sets.
Adrian M.
"HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> My stored procedure generates two results:
> 1) declares a variable executes a view to obtain date driven data which
> has
> a begin and end
> 2) selects the data associated with date driven info
> When RS executes the stored procedure I only get the first set results
> (the
> date information).
> Can RS handle multiple results from a stored procedure? If so is there any
> information to read.
> I can change my stored procedure with a couple joins but I am curious how
> RS
> works (I'm a newbee).
> Sincerely|||Thanks for the info
"Adrian M." wrote:
> Sorry...RS doesn't support multiple result sets.
> Adrian M.
> "HelpInRS" <HelpInRS@.discussions.microsoft.com> wrote in message
> news:CDADB1FA-43D3-40A0-BD94-CFD98F9FAFCE@.microsoft.com...
> > My stored procedure generates two results:
> > 1) declares a variable executes a view to obtain date driven data which
> > has
> > a begin and end
> > 2) selects the data associated with date driven info
> >
> > When RS executes the stored procedure I only get the first set results
> > (the
> > date information).
> >
> > Can RS handle multiple results from a stored procedure? If so is there any
> > information to read.
> >
> > I can change my stored procedure with a couple joins but I am curious how
> > RS
> > works (I'm a newbee).
> >
> > Sincerely
>
>