469,604 Members | 2,235 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,604 developers. It's quick & easy.

Stored procedure calling another stored procedure_

Hi all,

I have a stored procedure that return a resultset
e.g. stored proc: get_employee_details
select emp_id, emp_name, emp_salary, emp_position
from empoloyee

I would like to write another stored procedure that executes the above
stored procedure - returning the same number of records but it will
only show 2 columns
e.g. new stored proc: get_employee_pay -- executes
get_employee_details
I only need to know emp_id, emp_salary.

How can this be done in sql stored procedure?

Thanks,
June Moore.
Jul 20 '05 #1
2 27860
June,

I would make the first stored proc a UDF (user defined function) that
returns a table. This allows the second stored proc to use the UDF in the
From clause as if it were a table. You can then extract just the fields you
need in the Select statement. For example:

The second stored proc would contain the following SQL:

Select emp_id, emp_salary
From get_employee_details (assumes get_employee_details is a Table-Values
UDF)

See "Rewriting Stored Procedures as Functions" in BOL

J.R.
Largo SQL Tools
The Finest Collection of SQL Tools Available
http://www.largosqltools.com

"June Moore" <ju******@yahoo.com.au> wrote in message
news:e5**************************@posting.google.c om...
Hi all,

I have a stored procedure that return a resultset
e.g. stored proc: get_employee_details
select emp_id, emp_name, emp_salary, emp_position
from empoloyee

I would like to write another stored procedure that executes the above
stored procedure - returning the same number of records but it will
only show 2 columns
e.g. new stored proc: get_employee_pay -- executes
get_employee_details
I only need to know emp_id, emp_salary.

How can this be done in sql stored procedure?

Thanks,
June Moore.

Jul 20 '05 #2
[posted and mailed, please reply in news]

June Moore (ju******@yahoo.com.au) writes:
I have a stored procedure that return a resultset
e.g. stored proc: get_employee_details
select emp_id, emp_name, emp_salary, emp_position
from empoloyee

I would like to write another stored procedure that executes the above
stored procedure - returning the same number of records but it will
only show 2 columns
e.g. new stored proc: get_employee_pay -- executes
get_employee_details
I only need to know emp_id, emp_salary.

How can this be done in sql stored procedure?


I have an article on my web site that discusses this:
http://www.algonet.se/~sommar/share_data.html.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Rhino | last post: by
2 posts views Thread by Kent Lewandowski | last post: by
3 posts views Thread by louise raisbeck | last post: by
9 posts views Thread by fniles | last post: by
reply views Thread by pompeyoc | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.