SQL - Use 1 column of data from a Stored Proc as an input into 2nd Stored Proc

Hi Experts!

I have 2 stored procs:

GetActiveSuppliers
GetOrderStatus

The GetActiveSuppliers takes no input parameters and simply returns 3 columns:

SupplierID
SupplierName
SupplierEmail

The 2nd stored proc, GetOrderStatus, is more involved and returns a bunch of data based on a single parameter - SupplierID.

Today, I was asked to generate a master Order Status Report based on ALL Suppliers.
To do that, I've cloned the GetOrderStatus into a new stored proc called GetOrderStatusAll.
Rather than pass in a single supplier ID, I'd like it to use ALL the SupplierIDs from the results of GetActiveSuppliers proc above
However, I don't need the SupplierName and SupplierEmail columns, just the SupplierID.
I've changed the WHERE clause so it will read as:

WHERE SUPPLIERID IN
(
1,2,3,4,5
)

The tricky part is I'm not sure how to call that GetActiveSuppliers proc from within this new stored proc and explicitly pass a comma-separated list of just supplier IDs into the new stored proc's Where clause.

Any thoughts?  Thanks!
dpmoneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
If you could, I'd clone proc GetActiveSuppliers into an in-line table-value function instead.

Then it could be joined directly into the query that gets the Order Status.

If you can't do that, have the GetActiveSuppliers proc itself call the GetOrderStatusAll proc.  There's no point in going thru the hassle of separately passing them in if the code needs them all anyway.

Finally, if you can't do it in the proc, then create your own table data type, fill it by claling the GetActiveSuppliers proc yourself, and pass the table variable to the GOSA proc.
0
dpmoneyAuthor Commented:
Thank you for the input, but I just read these articles and am still having some difficulty mapping it back to exactly what I'm trying to accomplish.  Doesn't seem like an exact match.

I'm not clear how to select 1 column of data from the results of a stored procedure from within another stored procedure.  Can you provide a very simply example of that in T-SQL?

I'll get it started:

GetActiveSuppliers stored proc returns:

SupplierID, SupplierName, SupplierEmail
1,ABC Builders,abc@here.far
2,XYX Concrete,xyz@here.far
3,JJ Carpeting,jj@here.far

From within GetOrderStatus stored proc, how can I get the supplier IDs 1, 2, and 3 from GetActiveSuppliers into the WHERE Clause so it appears as below?

Where SupplierID in
(1,2,3)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pcelbaCommented:
I did not read the articles but the solution should be very easy, e.g.

 -- Create a temp table having the structure equal to the first SP output:
CREATE TABLE #tst (SupplierID int, SupplierName varchar(200), SupplierEmail varchar(200))
-- Populate table rows
INSERT INTO #tst
  EXEC dbo.GetActiveSuppliers
-- Use this table in your new SP, e.g.:
SELECT <something> FROM <sometable> 
  WHERE SupplierID IN (SELECT SupplierID FROM #tst)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
--Ok so....you can EXEC a SP and put the results into a temp table for instance then use that to build the "@list" to be passed into the second stored proc.

--So will be something like pseudo code below:

CREATE TABLE #templist (SuplierID sysname,SupplierName sysname, SupplierEmail sysname)
INSERT INTO #templist EXEC GetActiveSuppliers;
SELECT * FROM #templist;


--then you can use only that SuplierId column to build the list:


create type T_SuppIds AS TABLE
 (SuplierID int)
go
create procedure usp_example
 @SuppIds T_SuppIds READONLY
AS
 SELECT *
 FROM @SuppIds
GO
declare @SuppIds T_SuppIds

insert into @SuppIds
select top 10 SuplierId
from #templist

exec usp_example @SuppIds=@SuppIds
0
Brian CroweDatabase AdministratorCommented:
If GetActiveSuppliers is  that simple it should probably be a view or just reproduced in the second procedure.  You are often better off duplicating code in SQL rather than trying to make it modular.
0
dpmoneyAuthor Commented:
Thanks to everyone for their great feedback.  Points are going to pcelba because the answer provided was the first to provide EXACTLY what I needed and it worked perfectly!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.