Calling a stored procedure by another stored procedure 
Author Message
 Calling a stored procedure by another stored procedure

Hi,

I've a problem calling a stored procedure withing another
stored procedure. Both have same parameters defined but
when I run the parent stored proc. it gives a error
message "Invalid object name 'dbo.sp_clpcs'." I need some
how to run this and please help me get through on this.

// Parent procedure
CREATE PROCEDURE sp_parsumry

AS
SELECT dbo.Parcel.PA_no, dbo.Parcel.PA_date,
dbo.Parcel.PA_invno, dbo.sp_clpcs.clpcsiss,
dbo.sp_clpcs.clctsiss

FROM dbo.Parcel INNER JOIN
        dbo.Suppliers ON dbo.Parcel.SP_ID =
dbo.sp_clpcs.PA_no


GO

//sub procedure

CREATE PROCEDURE sp_clpcs

AS
SELECT dbo.Production.PA_no, SUM(dbo.Production.PR_pcsiss)
AS clpcsiss, SUM(dbo.Production.PR_ctsiss) AS clctsiss,
dbo.Production.SC_no
FROM dbo.Production

dbo.Production.SC_no = 1
GROUP BY dbo.Production.PA_no, dbo.Production.SC_no

GO



Sat, 11 Sep 2004 17:53:46 GMT
 Calling a stored procedure by another stored procedure

Damith,
You can not use a stored procedures in Select statements. there are work
arounds, but those are resource intensive.

If you are in SS2k, you might want to use a UDF instead.

the other option is to use a Temp table to pass the result set between
stored procedures

Eg.

GO

AS
Create table #Temp(PA_no ....,PA_date...)

-- and use #Temp  in your join
Drop table #Temp
GO

If you post the schema, sample data and tell us what exactly you want to
do, I'm sure someone here will be able to give you a clever solution.

--
hth
Hirantha S. Hettiarachchi MCSD,MCDBA,MCSA,OCP
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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sat, 11 Sep 2004 18:41:45 GMT
 Calling a stored procedure by another stored procedure
I'm using crstal report 8 to view this report. I was using
MS Access views with parameters. I called only one view to
the CR 8. But that View was a collection of views.
What I want now is the samething from SQL Server 2000. I
was trying to use stored procedures doing the same thing.
But it's failing when I called the another stored
proceduew.

Pls. let me know any other solutions to solve this problem.

Thanks

Quote:
>-----Original Message-----
>Damith,
>You can not use a stored procedures in Select statements.
there are work
>arounds, but those are resource intensive.

>If you are in SS2k, you might want to use a UDF instead.

>the other option is to use a Temp table to pass the
result set between
>stored procedures

>Eg.

>GO
>CREATE PROCEDURE sp_parsumry

>AS
>Create table #Temp(PA_no ....,PA_date...)

>-- and use #Temp  in your join
>Drop table #Temp
>GO

>If you post the schema, sample data and tell us what
exactly you want to
>do, I'm sure someone here will be able to give you a
clever solution.

>--
>hth
>Hirantha S. Hettiarachchi MCSD,MCDBA,MCSA,OCP
>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.

>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.



Sun, 12 Sep 2004 11:21:55 GMT
 Calling a stored procedure by another stored procedure
Damith,

you cant use a stored procedure instead of view in a select
statement.You can use either of the previously suggested methods to
achieve what you want. without seeing the table schema I cant suggest
anything else.

--
hth
Hirantha S. Hettiarachchi MCSD,MCDBA,MCSA,OCP
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.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 12 Sep 2004 11:51:42 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Calling Stored Procedure that calls Stored Procedure in ASP

2. Calling a Java Stored Procedure from another Java Stored Stored Procedure

3. Stored Procedure calling Stored Procedure

4. Call a stored procedure from another stored procedure

5. Calling a stored procedure with parameters from another stored procedure

6. Calling a Stored Procedure from a Stored Procedure

7. How to call an Oracle Stored procedure from MSSQL Server Stored Procedure

8. Calling a stored procedure with parameters from another stored procedure

9. Stored procedure calls multiple stored procedures

10. Calling stored procedures from another stored procedure

11. Call a stored procedure in another stored procedure

12. return select values in stored procedure to a calling stored procedure


 
Powered by phpBB® Forum Software