SQL Server - Stored Procedure - Another SP As Value

Question

Hi

I have an Update stored procedure and im asking if another stored procedure can be called with parameters to collect a value required for the first stored procedure?

Example, I edited row 21 to what i was thinking. Its passing the parameters @Job _id & @item_id to another stored procedure to get the max value

@job_id int = NULL,  
@item_id int = NULL,  
@item_letter varchar(10) = NULL,  
@item_qty int = NULL,  
@description varchar(MAX) = NULL,  
@rev_no int = NULL,  
@rev_date date = NULL,  
@revision_by varchar(MAX) = NULL,  
@requested_by varchar(MAX) = NULL,  
@approved_by varchar(MAX) = NULL,  
@rev_comments varchar(MAX) = NULL,  
@Product varchar(MAX) = NULL  
  
INSERT INTO [dbo].[tbl_MyTable]  
		SELECT		* 		  
		FROM		[dbo].[tbl_MyTableTemplate]  
		WHERE		[job_id] = @job_id AND [item_id] = @item_id  
  
UPDATE		[dbo].[tbl_MyTable]  
SET			[job_id] = @job_id, [item_id] = @item_id, [item_letter] = @item_letter, [item_qty] = @item_qty, [description] = @description,   
					[rev_no] = (SELECT [rev_no] FROM [dbo].[usp_tbl_Item_ListSelectMaxOf] WHERE ([job_id] = @job_id AND [item_id] = @item_id)),   
					[revision_by] = @revision_by,   
					[requested_by] = @requested_by, [approved_by] = @approved_by, [rev_comments] = @rev_comments,  
					[product] = @Product  
WHERE		[itemlist_id] = SCOPE_IDENTITY()  

Thanks

Answer 1

UPDATE

I have managed to do this with a function like this
@Job _id int = NULL,
@item_id int = NULL,
@item_letter varchar(10) = NULL,
@item_qty int = NULL,
@description varchar(MAX) = NULL,
@rev _no int = NULL,
@rev _date date = NULL,
@revision_by varchar(MAX) = NULL,
@requested_by varchar(MAX) = NULL,
@approved_by varchar(MAX) = NULL,
@rev _comments varchar(MAX) = NULL,
@tutor varchar(MAX) = NULL

 INSERT INTO [dbo].[tbl_MyTable]  
SELECT        *           
FROM        [dbo].[tbl_MyTableTemplate]  
WHERE      [job_id] = @job_id AND [item_id] = @item_id  
      
 UPDATE    [dbo].[tbl_MyTable]  
 SET           [job_id] = @job_id, [item_id] = @item_id, [item_letter] = @item_letter, [item_qty] = @item_qty, [description] = @description,   
                  [rev_no] = dbo.[GetTheMaxOf(@job_id, @Item_id) + 1,   
                  [revision_by] = @revision_by,   
                  [requested_by] = @requested_by, [approved_by] = @approved_by, [rev_comments] = @rev_comments,  
                  [product] = @Product  
 WHERE     [itemlist_id] = SCOPE_IDENTITY()  

with my function like this
ALTER FUNCTION [dbo].[GetTheMaxOf]
(@jobid int, @itemid int) RETURNS int

AS  
  
BEGIN  
	DECLARE @MaxRev int  
	SELECT @MaxRev = (SELECT MAX(rev_no) AS MaxOf FROM dbo.tbl_MyTable  
						GROUP BY job_id, item_id  
  
	HAVING				(dbo.tbl_MyTable.job_id = @jobid OR @jobid IS NULL) AND (dbo.tbl_MyTable.item_id = @itemid OR @itemid IS NULL))  
  
	RETURN @MaxRev  
END  

Answer 2

You don't need a separate function, if you're selecting a row from a template for insertion, you can make that new ID as one extra column, e.g.

;with cte as (select *, max(Rev_No) over () as MaxRev_No
from myList)

select *, isnull(MaxRev_No,0) + 1 as NewRevision
from cte where Rev_No = @rev _no;

Answer 3

Hi

Thanks for the reply. sorry if its unclear, i'll try to recap

In stored procedure 1, i am adding a revision from another table. The rev_no can either be the MaxOf rev_no from that table or any lower number depending on which revision is being used as the new revision template. I am passing the rev_no parameter into the stored procedure to use in the WHERE clause to get the copied row.

Then i need to find the MaxOf rev_no so that i can add 1 to it and make it the latest revision.

for example
My revision list shows 3 rows, each revision is numbered 1, 2 & 3
The next revision would be 4
i could select revision 1 as thats the row i want to copy from and that would be copied as a new revision with a new rev_no as 4
i need to collect the MaxOf from a stored procedure or function which in this case, would return 3. I would then add 1 to it and make rev_no 4

Thanks

Answer 4

Your question is a bit unclear, but I believe you need to use OUTPUT clause of the insert command to capture details of the inserted row(s) [There is a possibility of more than one row] and then do the UPDATE using that output table.