Stored procedure calling another stored procedure_

[June Moore]

Hi all,

I have a stored procedure that return a resultset
e.g. stored proc: get_employee_de tails
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_pa y -- executes
get_employee_de tails
I only need to know emp_id, emp_salary.

How can this be done in sql stored procedure?

Thanks,
June Moore.

[Largo SQL Tools]

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_de tails (assumes get_employee_de tails 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.goo gle.com...

Hi all,

I have a stored procedure that return a resultset
e.g. stored proc: get_employee_de tails
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_pa y -- executes
get_employee_de tails
I only need to know emp_id, emp_salary.

How can this be done in sql stored procedure?

Thanks,
June Moore.

[Erland Sommarskog]

[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_de tails
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_pa y -- executes
get_employee_de tails
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