Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT 
Author Message
 Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

I apologize in advance for the long post and if I don't make any sense but I
will try to explain my problem:

I am having a hard time figuring out how to retrieve an output parameter
from a dynamically generated stored proc call within another stored proc.

We're running SQL Server 7.

The problem: several stored procedures need to get a return value from a
second stored procedure on the same database that calls yet another stored
procedure on a linked server.  This final stored procedure on the linked
server returns a value and must pass it back through to the first proc.

The kicker is that the 'middle' stored proc that calls the linked server
stored proc gets what linked server it is dynamically as a parameter.  So I
have to dynamically build an 'execute' string.  And this is where I am
having trouble concatenating on the OUTPUT portion.

So some simplified code kind of looks like this:

On Server #1

CREATE PROCEDURE DoSomethingThatNeedsKey AS




BEGIN




END

In the same database...


AS

BEGIN

END

On Server #2:


AS
BEGIN


END

Now I would expect when I get back to the 'DoSomethingThatNeedsKey'

The code fails in 'GetNewKey1' proc, and gives me an error of

"Must declare the variable 'NewKey1' "

I thought that NewKey1 was already declared in the parameter list!  Am I not



value and move it outside of the quotes, I get a "Cannot use the OUTPUT
option when passing a constant to a stored procedure." error.

I am definitely a T-SQL novice, but I have tried everything I can think of:
creating a cursor in GetNewKey1, trying to do an sp_ExecuteSQL call, but
nothing seems to work.  Either I get the errors above or I just can't
compile.  All of the examples that I have seen that try to pass back an
output parameter in dynamically generated calls are always using SELECT
queries and not EXECUTES to other procs.

I have used this technique in the past and it always worked.  But that was
without OUTPUT tacked on.

Am I even making sense?  Is there a better way to set a variable to the
result of an EXECUTE statement?

Any and all help sincerely appreciated.

-David



Wed, 14 Jul 2004 04:15:29 GMT
 Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

David,

Let me apologize in advance for not providing a more detailed answer, but
basically the problem is that EXEC, when used to execute dynamic SQL, acts
like another completely new connection has been opened to SQL Server ...
that's not true, but it's a convenient fiction for the moment. Consequently,

dynamic SQL.

Try using the sp_ExecuteSQL function instead:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q262499

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I apologize in advance for the long post and if I don't make any sense but
I
> will try to explain my problem:

> I am having a hard time figuring out how to retrieve an output parameter
> from a dynamically generated stored proc call within another stored proc.

> We're running SQL Server 7.

> The problem: several stored procedures need to get a return value from a
> second stored procedure on the same database that calls yet another stored
> procedure on a linked server.  This final stored procedure on the linked
> server returns a value and must pass it back through to the first proc.

> The kicker is that the 'middle' stored proc that calls the linked server
> stored proc gets what linked server it is dynamically as a parameter.  So
I
> have to dynamically build an 'execute' string.  And this is where I am
> having trouble concatenating on the OUTPUT portion.

> So some simplified code kind of looks like this:

> On Server #1

> CREATE PROCEDURE DoSomethingThatNeedsKey AS




> BEGIN




> END

> In the same database...

> CREATE PROCEDURE GetNewKey1



> AS

> BEGIN


> END

> On Server #2:

> CREATE PROCEDURE GetNewKey2


> AS
> BEGIN


> END

> Now I would expect when I get back to the 'DoSomethingThatNeedsKey'

> The code fails in 'GetNewKey1' proc, and gives me an error of

> "Must declare the variable 'NewKey1' "

> I thought that NewKey1 was already declared in the parameter list!  Am I
not



> value and move it outside of the quotes, I get a "Cannot use the OUTPUT
> option when passing a constant to a stored procedure." error.

> I am definitely a T-SQL novice, but I have tried everything I can think
of:
> creating a cursor in GetNewKey1, trying to do an sp_ExecuteSQL call, but
> nothing seems to work.  Either I get the errors above or I just can't
> compile.  All of the examples that I have seen that try to pass back an
> output parameter in dynamically generated calls are always using SELECT
> queries and not EXECUTES to other procs.

> I have used this technique in the past and it always worked.  But that was
> without OUTPUT tacked on.

> Am I even making sense?  Is there a better way to set a variable to the
> result of an EXECUTE statement?

> Any and all help sincerely appreciated.

> -David



Wed, 14 Jul 2004 09:20:05 GMT
 Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT
[posted and mailed, please reply in news]

Quote:

> The kicker is that the 'middle' stored proc that calls the linked server
> stored proc gets what linked server it is dynamically as a parameter.
> So I have to dynamically build an 'execute' string.  And this is where I
> am having trouble concatenating on the OUTPUT portion.
>...
> CREATE PROCEDURE GetNewKey1



> AS

> BEGIN


> END

While BP pointed you to an article that solves the general problem for
dynamic SQL, there no need for it in this case. There is an over-looked
feature in T-SQL that you could use. Watch this:

   CREATE PROCEDURE GetNewKey1



   AS



That is, only the SP name is dynamic. The rest is static code and all
variables are within the same scope. (That's why you get a problem with
EXEC(), the dynamic SQL is not part of the procedure but has its own
scope.)

--
Erland Sommarskog, Abaris AB

SQL Server MVP



Fri, 16 Jul 2004 06:24:44 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Calling a stored proc within a stored proc

2. Calling a Stored proc within a stored proc

3. Calling another stored proc within a stored proc

4. populating a temptable from a stored proc within a store proc

5. Create large Stored proc from within stored proc

6. How to get results from Stored Proc within a Stored Proc

7. newbie trying to execute a stored proc from within a stored proc

8. Stored Proc within a Stored Proc

9. Exec store proc within a store proc

10. Stored Proc Calling Another Stored Proc

11. calling stored proc from stored proc via variable


 
Powered by phpBB® Forum Software