Return Get New id in SQL for new request sumission

 USE [BIM_Communication]

GO

/****** Object:  StoredProcedure [dbo].[GetNewChangeId]    Script Date: 8/24/2021 6:15:58 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[GetNewChangeId]

AS

BEGIN

    DECLARE @COUNTVAR INT

DECLARE @CHANGEID VARCHAR(MAX)

SELECT  @COUNTVAR = COUNT(*) FROM [dbo].[ChangeDetails]

IF @COUNTVAR <> 0

  SET @CHANGEID =  'CHG_'+RIGHT('000000' + CONVERT(nvarchar(20), @COUNTVAR + 1), 6)

    ELSE

    SET @CHANGEID =  'CHG_'+RIGHT('000000' + CONVERT(nvarchar(20), 1), 6) 

   SELECT @CHANGEID

END

===================================================================

Sp sample

USE [AGSBIM_APPS_DEV]

GO

/****** Object:  StoredProcedure [dbo].[USP_GetPBGByEmployeeId]    Script Date: 8/24/2021 6:19:27 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[USP_GetPBGByEmployeeId]

@employeeId VARCHAR(30)

AS

BEGIN

SET NOCOUNT ON;

Declare @pbg varchar(max);

Declare @greenbook varchar(max);

SELECT @greenbook = GREENBOOK, @pbg = PBG FROM [dbo].[USER_PBG_MAPPING] WHERE employeeid = @employeeId

IF @greenbook = '-1' AND @pbg ='-1'

SELECT DISTINCT(NAME) AS PBG from [dbo].[PBG]

ELSE 

BEGIN

Create Table #MyAllPbgs(PBG VARCHAR(50))

INSERT INTO #MyAllPbgs SELECT DISTINCT PBG FROM [dbo].[USER_PBG_MAPPING] WHERE employeeid = @employeeId AND PBG != '-1'

INSERT INTO #MyAllPbgs SELECT DISTINCT NAME FROM [dbo].[PBG] WHERE GREENBOOK IN (SELECT DISTINCT GREENBOOK FROM [dbo].[USER_PBG_MAPPING] WHERE employeeid = @employeeId AND PBG = '-1')

SELECT * FROM #MyAllPbgs

END

END