calling stored proc from stored proc via variable

calling stored proc from stored proc via variable

Post by brank » Thu, 13 Nov 2003 23:30:56



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

 
 
 

calling stored proc from stored proc via variable

Post by William Bartholome » Thu, 13 Nov 2003 23:43:00




> 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

 
 
 

calling stored proc from stored proc via variable

Post by brank » Fri, 14 Nov 2003 00:25:30


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

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

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

 
 
 

calling stored proc from stored proc via variable

Post by William Bartholome » Fri, 14 Nov 2003 00:57:58




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

 
 
 

calling stored proc from stored proc via variable

Post by Jonathan Jesperse » Fri, 14 Nov 2003 16:29:22


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




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

 
 
 

1. Stored Proc Calling Another Stored Proc

I have a table which has a child/parent relationship. I wrote a stored
procedure that returns all the children for any given record. I now want to
use this stored procedure in another stored procedure that will delete the
records retrieved from the first stored procedure. It sounds so simple, but
I'm stumped. How can I store the results from the first stored procedure
into a cursor that I can work with in the second stored procedure?

2. no question

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

4. US-CO-DESIGNER 2000 MODELING

5. Calling a stored proc within a stored proc

6. Turning a button invisible

7. Calling Stored Proc from other Stored Proc

8. Best data control for IE5?

9. a stored proc calling another stored proc

10. Calling another stored proc within a stored proc

11. Using a stored proc to call another stored proc

12. Calling a Stored proc within a stored proc

13. Calling Stored Proc from another Stored Proc