Stored Procedure - Get value from another SP

Question

Stored Procedure - Get value from another SP

NachitoMax 416

Hey

Im sure this is possible, i just need to find the right way to do it.

Overview
An existing database created by a previous guy years ago, converted from Access. I cannot change the database or table structures.

Requirement
Return job info from a job table using the job_id
add a boolean value to the return row where only a string value can be captured from another stored procedure

Stored Procedure 1

ALTER PROC [dbo].[usp_GetJobList]   
    @job_id int = null,  
 @thermal_break bit = False  
  
AS   
 SET NOCOUNT ON   
 SET XACT_ABORT ON    
  
SELECT dbo.job.job_id, dbo.job.name, dbo.job.short_description, @thermal_break AS thermal_break  
FROM        dbo.job  
WHERE dbo.job.job_id = @job_id  
ORDER BY     dbo.job.job_id DESC

Stored procedure 2

ALTER PROC [dbo].[usp_GetThermalBreak]   
    @job_id int  = NULL  
AS   
 SET NOCOUNT ON   
 SET XACT_ABORT ON    
  
SELECT       dbo.lookup_in_ex.description_memo AS thermal_break  
FROM         dbo.job_in_ex INNER JOIN  
             dbo.lookup_in_ex ON dbo.job_in_ex.in_ex_id = dbo.lookup_in_ex.id  
WHERE       (dbo.job_in_ex.job_id = @job_id) AND (dbo.job_in_ex.in_ex_id = 43)

Objective
GetJobList returns a single row. I need to collect the thermal_break value from GetThermalBreak and set @greg@thermalcloudsolutions.com _break (or an alternative method)

GetThermalBreak returns a single string value that is either 'thermal break' or nothing WHERE job_id = @Job _id

i need to get the value from GetThermalBreak
If the return value = 'thermal_break', return True
Else return False

How can i set this up in procedure 1 so that i can call GetThermalBreak using the job_id and return its value as a true or false boolean and then add the return value to the datarow so i get something like

job_id name thermal_break
123456 some job description True
246810 some other job False

Thanks

LiHongMSFT-4306 31,621 Reputation points

2022-08-19T07:17:03.973+00:00

Hi @NachitoMax
Have you validate all the answers so far and provide any update?
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Answer accepted by question author

Isabellaz-1451 3,616

Hi

Base on Erland's provided thread,you can merge the 2 SP,like below:

ALTER PROC [dbo].[usp_GetJobList]   
     @job_id int = null  
      
 AS   
  SET NOCOUNT ON   
  SET XACT_ABORT ON    
  
 DECLARE @thermal_break bit   
BEGIN TRANSACTION  
SELECT  @thermal_break = top 1 dbo.lookup_in_ex.description_memo AS thermal_break  
 FROM         dbo.job_in_ex INNER JOIN  
              dbo.lookup_in_ex ON dbo.job_in_ex.in_ex_id = dbo.lookup_in_ex.id  
 WHERE       (dbo.job_in_ex.job_id = @job_id) AND (dbo.job_in_ex.in_ex_id = 43)  
COMMIT TRANSACTION  
  
 SELECT dbo.job.job_id, dbo.job.name, dbo.job.short_description, @thermal_break AS thermal_break  
 FROM        dbo.job  
 WHERE dbo.job.job_id = @job_id  
 ORDER BY     dbo.job.job_id DESC

Best Regards,
Isabella

0 comments

4 additional answers

Your answer

LiHongMSFT-4306 31,621 Reputation points

2022-08-19T07:17:03.973+00:00

Hi @NachitoMax
Have you validate all the answers so far and provide any update?
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Answer 1

Hi @NachitoMax
If you want to pass value from one SP to another SP, then you can try OUTPUT, like this:

 ALTER PROC [dbo].[usp_GetThermalBreak]   
     @job_id int   
     @thermal_break varchar(20) OUTPUT  
 AS   
  SET NOCOUNT ON   
  SET XACT_ABORT ON    
   
 SELECT @thermal_break = CASE WHEN dbo.lookup_in_ex.description_memo ='thermal_break'   
                              THEN 'TRUE'  
                              ELSE 'FALSE' END  
 FROM dbo.job_in_ex INNER JOIN dbo.lookup_in_ex ON dbo.job_in_ex.in_ex_id = dbo.lookup_in_ex.id  
 WHERE (dbo.job_in_ex.job_id = @job_id) AND (dbo.job_in_ex.in_ex_id = 43)  
 GO  
      
 ALTER PROC [dbo].[usp_GetJobList]   
     @job_id int = null  
 AS  
 BEGIN  
   SET NOCOUNT ON   
   SET XACT_ABORT ON    
   DECLARE @thermal_break varchar(20)  
   EXEC usp_GetThermalBreak @job_id, @thermal_break OUTPUT  
     
   SELECT dbo.job.job_id, dbo.job.name, dbo.job.short_description, @thermal_break AS thermal_break  
   FROM dbo.job  
   WHERE dbo.job.job_id = @job_id  
   ORDER BY dbo.job.job_id DESC  
 END  
 GO

Best regards,
LiHong

Answer 2

Erland Sommarskog 134.4K MVP Volunteer Moderator

The most direct solution may be to use INSERT @Bluemchen (...) EXEC sp to capture the output, but this method is fragile.

I have an article on my web site How to Share Data between Stored Procedures that discuss the different options in more detail.

0 comments

Answer 3

Tom Phillips 17,786

If those are just select statements, it would be better and easier to use as a view or a function.

0 comments

Answer 4

ENGIN SENOL 1

Create a Function (second_sp_function) to call second SP with Openquery with one parameter (JobID)
select * from openquery(Server, 'exec sp_who')
on the first query use "case when second_sp_function(JobID) = 'thermal_break' then True else False end as thermal_break" instead @greg@thermalcloudsolutions.com _break AS thermal_break

0 comments

Share via

Stored Procedure - Get value from another SP

4 additional answers

Your answer