Use two store Procedure

Question

I am using below store Procedure for inserting one row multiple time in database,

USE [SilverRags]
GO
/****** Object:  StoredProcedure [dbo].[SP_Inert_GRN]    Script Date: 1/5/2022 12:56:11 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[SP_Inert_GRN]
@GRN_ID int=null,
@Level_Four_ID int=null,
@GRN_Status varchar(50)=null,
@GRN_Date date=null,
@GRN_QTY int =null,
@EMp_ID int=null,
@GRN_Ref_No varchar(50)=null,
@ETime time(7)=null,
@Item_Code int = null,
@Item_Weight Decimal(10,2)=null,
@Item_Qty int =null,
@Item_Date date=null,
@item_Time time(7)=null,
@Pack_ID int =null,
@Del_Date date=null,
@Del_Time Time(7)=null,
@Del_ID int=null,
@D_ID int =null,
@U_Remarks varchar(150)=null,
@Truckweight decimal(10, 2)=null,
@caseno  int=null,
@Inv_no varchar(50)=null,
@Inv_Value decimal(10, 2)=null,
@Slip_Weight decimal(10, 2)=null,
@GRN_U_Date date=null,
@GRN_U_Time Time(7)=null,
@Item_U_Time Time(7)=null,
@Item_U_Date Date=null,
@Sup_Code int=null,
@ent_status varchar(50)=null,
@G_DID varchar(50)=null,
@blk_trans int=null,
@trans_date date=null,
@blk_Trans_date date=null,
@Trans_Time time(7)=null

as
begin
 SET NOCOUNT ON;

insert into tbl_GRN_Detail(GRN_ID ,Item_Code,Item_Weight,Item_Date,Item_Time,Sup_Code,ent_status,G_DID) 

 SELECT Top(@Item_Qty) @GRN_ID ,@Item_Code,@Item_Weight,@Item_Date,@Item_Time,@Sup_Code,@ent_status,@G_DID
FROM master.dbo.syscolumns



end

Now i want, before inserting each row from above SP,it execute below Store Procedure

ALTER Proc [dbo].[SP_Generate_GRND_ID] 

as begin



  ;WITH cte AS (SELECT 1 AS G_DID
                            UNION ALL
                            SELECT TOP (1) stuff(G_DID, 1, 2, '') + 1 AS G_DID
                            FROM     tbl_GRN_Detail AS t

                            ORDER BY G_DID DESC)
      SELECT TOP (1)  CONCAT('G-', G_DID)  AS G_DID
      FROM     cte

      ORDER BY G_DID DESC

end

Because i want unique G_DID in each row to be inserted in table.

Answer 1

Hi，@Analyst_SQL
How about modify [dbo].[SP_Inert_GRN],use left join when insert.
Or maybe have a try on Trigger like this:

IF OBJECT_ID('tri_AfterInsert')IS NOT NULL  
DROP TRIGGER tri_AfterInsert  
GO  
CREATE TRIGGER tri_AfterInsert ON tbl_GRN_Detail  
AFTER INSERT   
AS  
UPDATE tbl_GRN_Detail  
SET G_DID = CONCAT('G-',stuff(G_DID, 1, 2, '') +(SELECT COUNT(GRN_ID) FROM tbl_GRN_Detail WHERE GRN_ID=@GRN_ID))   

Best regards,
LiHong

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You can get the result of SP_Generate_GRND_ID in this manner:

create table #t(G_DID varchar(50)) -- (or define a local table variable)

insert #t
exec SP_Generate_GRND_ID

declare @g_did varchar(50)

select @g_did = G_DID from #t

Then pass @g_did to SP_Insert_GRN.

But you can also define a simpler function instead of procedure.

Make sure that SP_Generate_GRND_ID is not used incorrectly by multiple parallel tasks, otherwise the value will not be unique.