IF (ISNULL(@VAR1, 0) <> 0)
BEGIN
DECLARE #TEMP TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
BATCH VARCHAR(20)
)
INSERT INTO #TEMP EXEC(@VAR1)
DECLARE @COUNT INT
SELECT @COUNT=COUNT(BATCH) FROM #TEMP
DECLARE @BATCH VARCHAR(20)
IF(@COUNT > 0)
BEGIN
SET @EMPLOOPCOUNT = 1;
WHILE (@TEMPLOOPCOUNT <= @COUNT)
BEGIN
SELECT @BATCH = BATCH FROM #TEMP WHERE ID = @TEMPLOOPCOUNT
INSERT INTO BATCH_1_DATA
(
BATCH
)
VALUES
(
@BATCH
)
SET @TEMPLOOPCOUNT = @TEMPLOOPCOUNT + 1
END
END
END
Try this code in place of
create table #temp
(
id int identity(1,1) not null,
batch varchar(20)
)
insert into #temp exec(@var1)
declare @count int
declare @next int
select @count=COUNT(batch) from #temp
declare @batch varchar(20)
set @next=@count-1
if (@count>0)
begin
while(@next>=0)
begin
select top 1 @batch=batch from (select top (@count-@next) * from #temp order by id desc) as f order by f.id asc
insert into Batch_1_Data select @batch
set @next=@next-1
end