Run one SP from inside another hoping for a bid pay off

Question

User1824003892 posted

U am trying to run a stored procedure that takes an Id from another SP. The internal SP returns a 1 record or it may return 20 record, The reason for running it from inside another SP is  I'm  expecting that all the records from the internal SP would would be returned by the external SP. I am looking for confirmation  on that and maybe some help with the code.  I am looking for a way to pass the IN() result to the SP

DECLARE @jobId int
SELECT @jobId = Id FROM dbo.JobPass WHERE Id  IN (SELECT Id FROM dbo.JobPass WHERE dropoffTime is not null)
EXEC SP_JobStop_GetJobEZPassById @jobId 

Answer 1

User475983607 posted

Pass a table to the stored procedure.  

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15

You could also use a temp table.

Answer 2

User1824003892 posted

Create TYPE JobIdJobNumberTableType 
AS TABLE
( Id int,jobNumber int );
GO
DECLARE @jobNumber AS JobIdJobNumberTableType
INSERT INTO @jobNumber (Id, jobNumber)
SELECT [Id], jobNumber
FROM dbo.JobPass 
WHERE PickupTime IS Not Null;

SELECT Id,jobNumber FROM @jobId

EXEC SP_JobStop_GetJobEZPassById @jobId