calling stored proc from stored proc via variable 
Author Message
 calling stored proc from stored proc via variable
I would like to do something like this



GO

Is that doable?

In case the above makes no sense here is what I want
- i want to pass a string and int to a stored proc
- string is a another stored proc name
- int is a stored proc param

so if I do
pGetData pGetText, 10

i want it to execute
pGetText 10

thanks bunch,
branka



Sun, 30 Apr 2006 22:30:56 GMT
 calling stored proc from stored proc via variable



Quote:
> I would like to do something like this



> GO

> Is that doable?

> In case the above makes no sense here is what I want
> - i want to pass a string and int to a stored proc
> - string is a another stored proc name
> - int is a stored proc param

> so if I do
> pGetData pGetText, 10

> i want it to execute
> pGetText 10

> thanks bunch,
> branka

Beware though... don't forget that the caller can call any stored procedure
that is capable of taking a single int as a parameter... And if I remember
my SQL security correctly it runs with the permission of the person that
created the stored procedure. So this probably isn't a good idea. You can
make it minimally safer by forcing some kind of prefix to the stored
procedure name. The second example shows this.

CREATE PROCEDURE pGetData


AS
SET NOCOUNT ON


GO

CREATE PROCEDURE pGetData


AS
SET NOCOUNT ON



GO

Regards,

William D. Bartholomew



Sun, 30 Apr 2006 22:43:00 GMT
 calling stored proc from stored proc via variable
This does not work for some reason :(

if I call (from Quary Analyzer)
pGetData sProcName, iProcPara with your 2nd example
I get the following
Could not find stored procedure 'm'.

seems like it is not reading the entire stored proc name
or something.

thanks a bunch,
branka

Quote:
>-----Original Message-----



>> I would like to do something like this


int

>> GO

>> Is that doable?

>> In case the above makes no sense here is what I want
>> - i want to pass a string and int to a stored proc
>> - string is a another stored proc name
>> - int is a stored proc param

>> so if I do
>> pGetData pGetText, 10

>> i want it to execute
>> pGetText 10

>> thanks bunch,
>> branka

>Beware though... don't forget that the caller can call

any stored procedure

- Show quoted text -

Quote:
>that is capable of taking a single int as a parameter...
And if I remember
>my SQL security correctly it runs with the permission of
the person that
>created the stored procedure. So this probably isn't a
good idea. You can
>make it minimally safer by forcing some kind of prefix to
the stored
>procedure name. The second example shows this.

>CREATE PROCEDURE pGetData


>AS
>SET NOCOUNT ON


>GO

>CREATE PROCEDURE pGetData


>AS
>SET NOCOUNT ON



>GO

>Regards,

>William D. Bartholomew
>.



Sun, 30 Apr 2006 23:25:30 GMT
 calling stored proc from stored proc via variable


Quote:
> This does not work for some reason :(

> if I call (from Quary Analyzer)
> pGetData sProcName, iProcPara with your 2nd example
> I get the following
> Could not find stored procedure 'm'.

> seems like it is not reading the entire stored proc name
> or something.

> thanks a bunch,
> branka

Sorry did not notice you didn't have a size on your varchar parameter in
which case it defaults to 1 character.


Regards,

William D. Bartholomew



Sun, 30 Apr 2006 23:57:58 GMT
 calling stored proc from stored proc via variable
I have a question along the same line as this one.  Here is my scenario:

I have 3 stored procedures: spA, spB, and spC.  Each of the procedures
accept multiple parameters.

In any given set of calls, spA will only execute once.  spB and spC can
execute multiple times, and not always the same number of times.

Also, I need these to execute as a transaction, but without the use of MTS.

So my setup is something like:

create procedure spZZ (parameters ...)
AS

-- Parse the parameters

begin tran
sp_execute (spA & parameters1)
sp_execute (spB & parameters2)
sp_execute (spB & parameters3)
sp_execute (spB & parameters4)
sp_execute (spC & parameters5)
sp_execute (spC & parameters6)
commit tran
GO

I know I can pass all the parameters for all procedures into spZZ.  My
question is: is this the best solution given my limitations?

Thanks,
Jonathan

Quote:



>>This does not work for some reason :(

>>if I call (from Quary Analyzer)
>>pGetData sProcName, iProcPara with your 2nd example
>>I get the following
>>Could not find stored procedure 'm'.

>>seems like it is not reading the entire stored proc name
>>or something.

>>thanks a bunch,
>>branka

> Sorry did not notice you didn't have a size on your varchar parameter in
> which case it defaults to 1 character.


> Regards,

> William D. Bartholomew



Mon, 01 May 2006 15:29:22 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Stored Proc Calling Another Stored Proc

2. How to call a stored Proc or Ext Stored proc /T-SQL UDF from VBScript

3. Calling a stored proc within a stored proc

4. Calling Stored Proc from other Stored Proc

5. a stored proc calling another stored proc

6. Calling another stored proc within a stored proc

7. Using a stored proc to call another stored proc

8. Calling a Stored proc within a stored proc

9. Calling Stored Proc from another Stored Proc

10. Calling stored proc from stored proc

11. Need Help on Dynamic Stored Proc Call within Stored Proc w/OUTPUT

12. Saving the resultset from a stored proc being called in another stored proc


 
Powered by phpBB® Forum Software