By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,919 Members | 1,047 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,919 IT Pros & Developers. It's quick & easy.

Stored procedure calling another stored procedure_

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
[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.