consuming result sets from dynamic queries within a cursor

Hi SQLers
I have the flowing scenario. I have a cursor that amongst other things
returns me the name of a stored procedure that needs to run and a parameter
to pass to it. Depending on whether the stored proc returns a result I then
decide what to do with the other data I get from the cursor. But I want to
suppress the result sets coming from the inner stored proc calls.
I basically want something like this in my cursor loop
insert into #tmp exec ( @.StoredProcedureName + ' ' + @.messageID )
if exists (select 1 from #tmp)
-- do something with cursor data
else
-- do something else with cursor data
-- where @.StoredProcedureName and @.messageID are data from the cursor
but I get an error saying invalid object #tmp
I cannot declare #tmp as a table variable as unfortunately the stored
proceedures called are different for different iterations of the cursor, tha
t
is @.StoredProcedureName changes and the result set from the different
procedures is different. I cannot make them the same as each of these proc
does a different job and they are called by different callers and so cannot
be changed.
and I dont want to say
exec ( @.StoredProcedureName + ' ' + @.messageID )
if (@.@.ROWCOUNT > 0)
-- do something
else
-- do something else
as the result set of the exec is then returned to the caller.
If anyone has any ideas I would be very greatful. I know some ways to solve
this in C# .NET but I was just wondering if there is a SQL solution. I expec
t
there is not but thought I would ask those that are likely to know.
Many thanks
DerekI don't understand why you would want to execute a stored proc dynamically
based on a cursor. TSQL does have IF statements for flow control but what
you are describing is a very procedural solution. Are you sure there isn't a
set-based SQL alternative to all this?
Read this article on how to do dynamic SQL (and why NOT to do it):
http://www.sommarskog.se/dynamic_sql.html
This one explains how you can process result sets from an SP:
http://www.sommarskog.se/share_data.html
David Portas
SQL Server MVP
--|||Hi David,
Thanks for your time.
Basically my cursor is finding all entries in a MessageReceipients table
that have NULL as their SentDateTime and once armed with the MessageID and
@.MessageType for these intended recipients, I need to call the appropriate
stored procedures (the actual stored proc will depend on the MessageType) to
see if these receipients can still have messages sent to them.
For example it is possible that a message receipient is in the
MessageRecipient table and that the mail system originally failed to send th
e
message but by the time we come to do this check (to resend the message) the
intended recipient my no longer want or be able to receive messages. This
fact can only be determined by calling the @.StoredProcedureName (in my
original code sample) and the reason this is dynamic is that the procedure
that needs to be checked to determin if a message can still be sent is
different for different message types.
I know I can solve this problem in my C# code and perhaps this is the place
to do it. For example I could just return all receipients that have got
potentially outstanding messages. And in the C# check the @.MessageType and
@.MessageID and then call the @.StoredProcedureName with @.MessageID to deterim
whether it is really appropriate to send the message to the receipient.
I was just hoping to not have to do this step in the C# application if I
could avoid it.
I also realise that re writing the various @.StoredProcedureName SPs would
make life easier but unfortunately they are used by other parts of the app
and so I cannot change them. So I would have to esentially duplicate there
functionality with the exception of how they return their results and that
would not be good from a maintenance perspective.
Having said that for now all I am doing in the C# application is looking at
the last result set returned from my SP as this is the result set that I
want. But this is only ok as the results sets are quite small and so I do no
t
need to worry to much about returning unwanted data to the C# application.
Hope that make things a little clearer
Many thanks
Derek
"David Portas" wrote:

> I don't understand why you would want to execute a stored proc dynamically
> based on a cursor. TSQL does have IF statements for flow control but what
> you are describing is a very procedural solution. Are you sure there isn't
a
> set-based SQL alternative to all this?
> Read this article on how to do dynamic SQL (and why NOT to do it):
> http://www.sommarskog.se/dynamic_sql.html
> This one explains how you can process result sets from an SP:
> http://www.sommarskog.se/share_data.html
> --
> David Portas
> SQL Server MVP
> --
>
>