Create large Stored proc from within stored proc

Create large Stored proc from within stored proc

Post by Barr » Sat, 12 Aug 2000 04:00:00



Is there a way to create a large stored procedure (contains unicode data
but > 4000 characters) from within a stored procedure.   I am
dynamically creating a stored procedure based on user defined tables and
columns.   Up to now this was working using the


procedure needs to be > 4000 unicode characters.
 
 
 

Create large Stored proc from within stored proc

Post by news.microsoft.co » Sat, 12 Aug 2000 04:00:00



oft.com...

> Is there a way to create a large stored procedure (contains
unicode data
> but > 4000 characters) from within a stored procedure.   I am
> dynamically creating a stored procedure based on user defined
tables and
> columns.   Up to now this was working using the


stored
> procedure needs to be > 4000 unicode characters.

You can combine strings, thus:

  declare




SQL>...'



The above piece of code will allow you to execute a piece of SQL
that
contains up to 12,000 Unicode characters. String expressions like
the
above yield a string result that exceeds the maximum allowed
length,
so the above works. However, assigning the string expression to a
variable
will result in truncation.

N.
--

 
 
 

1. 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

2. Window Size

3. Stored Proc within a Stored Proc

4. Datagrid format

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

6. How to upload to DAV servers using ADODB / OLE DB for Internet Publishing

7. Exec store proc within a store proc

8. replication, create snapshot group objects

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

10. Calling a Stored proc within a stored proc

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

12. Calling another stored proc within a stored proc

13. Calling a stored proc within a stored proc