How to generate number between two specified number in sql server store procedure?

It is quite possible that the CURSOR is causing database locking issues, in any event it is very slow and not a recommended approach.

An alternative to using CURSOR is to determine the number of records and use a WHILE loop (idea taken from, and further details at, SQL Cursors - how to avoid them[^])

I generated the following tables for this example

CREATE TABLE [SUB_tbl_Reports]
(
	code varchar(10),
	Pstart int,
	PEnd int
)
CREATE TABLE [SUB_tbl_Reports_Audit]
(
	code varchar(10),
	PIssueNumber int
)
insert into [SUB_tbl_Reports] values
				('A',  1, 5), 
				('B', 11, 15),
				('C', 21, 25),
				('D', 31, 35)

First step is to create a temporary table to contain the base data with an added Row Number

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN DROP TABLE #Temp END
SELECT *, ROW_NUMBER() OVER (ORDER BY code) AS ROWID INTO #temp FROM [SUB_tbl_Reports]

(You may be tempted to use an existing IDENTITY column on a table, but this will fail if any records have been deleted, leaving gaps in the ID numbers. Using Row_Number ensures that there are no gaps)

Now define and initialise the loop counter for the loop and capture the number of rows on the table

DECLARE @RowCount int
SET @RowCount = 1
DECLARE @NumRows int
SELECT @NumRows = (SELECT COUNT(*) FROM [SUB_tbl_Reports])

Now you can step through the table using the generated ROWID without locking the entire table up with a SQL WHILE loop.

DECLARE @code varchar(10)
DECLARE @PStart int
DECLARE @PEnd int
WHILE @RowCount <= @NumRows
BEGIN
    SELECT @code = code, @Pstart = Pstart, @PEnd = Pend
    from #temp where ROWID = @RowCount;

     -- ... do some stuff here

    SET @RowCount = @RowCount + 1
END

The "do some stuff here" in your case is "generate x table rows with numbers starting at value y where x and y can be derived from the original table". The easiest way to do this is to use a Common Table Expression (CTE) to generate a sequence of numbers from Pstart to Pend (extrapolated from Generating a Sequence in SQL[^])

WITH q AS
       (
       SELECT  @Pstart AS num
       UNION ALL
       SELECT num + 1
       FROM q
       WHERE num < @Pend
       )
   insert into [SUB_tbl_Reports_Audit] SELECT [#temp].code, num FROM [#temp], q WHERE ROWID = @RowCount
   OPTION (MAXRECURSION 0)

Note the MAXRECURSION hint on the select - default is 100 but the 0 removes any limitations. An alternative is to pick an appropriate number for the expected maximum number of rows you may need, but this will have to be hard-coded - a variable cannot be used.

The entire fiddle is reproduced below:

CREATE TABLE [SUB_tbl_Reports]
(
	code varchar(10),
	Pstart int,
	PEnd int
)

CREATE TABLE [SUB_tbl_Reports_Audit]
(
	code varchar(10),
	PIssueNumber int
)

insert into [SUB_tbl_Reports] values
				('A',  1, 15), 
				('B', 11, 15),
				('C', 21, 25),
				('D', 31, 35)


IF OBJECT_ID('tempdb..#Temp') IS NOT NULL BEGIN DROP TABLE #Temp END
SELECT *, ROW_NUMBER() OVER (ORDER BY code) AS ROWID INTO #temp FROM [SUB_tbl_Reports]

DECLARE @RowCount int
SET @RowCount = 1
DECLARE @NumRows int
SELECT @NumRows = (SELECT COUNT(*) FROM [SUB_tbl_Reports])

DECLARE @code varchar(10)
DECLARE @PStart int
DECLARE @PEnd int

WHILE @RowCount <= @NumRows
BEGIN
	SELECT @code = code, @Pstart = Pstart, @PEnd = Pend
	from #temp where ROWID = @RowCount;
	
	 WITH q AS
			(
			SELECT  @Pstart AS num
			UNION ALL
			SELECT num + 1
			FROM q
			WHERE num < @Pend
			)
		insert into [SUB_tbl_Reports_Audit] SELECT [#temp].code, num FROM [#temp], q WHERE ROWID = @RowCount
		OPTION (MAXRECURSION 0)

	SET @RowCount = @RowCount + 1
END

select * from [SUB_tbl_Reports_Audit]