Generate rownumber based on table result from an sp-CodePudding

So assume I have a stored procedure which has a parameter based on which columns are ordered and returned as a table.

I would like to insert to a new table same output but with a rownumber, in the same order which stored procedure returns values.

create table test
(
    ColA    varchar(50),
    ColB    varchar(50)
);

GO
INSERT INTO test(ColA,ColB)
VALUES('aaa','zzz'),('yyy','bbb');
GO

CREATE PROCEDURE TestProc
(
    @OrderCol VARCHAR(50)
)
AS
BEGIN
    SELECT * FROM test
    ORDER BY CASE @OrderCol WHEN 'A' THEN ColA WHEN 'B' THEN ColB END
END
GO

create table testRn
(
    RowNum  int,
    ColA    varchar(50),
    ColB    varchar(50)
);
GO

So as you can see there is a param is an SP @OrderCol and result is returned from an sp in ordered format, I just want to insert to a table with the same row number, but I can't use @OrderCol or alter a stored procedure.

Aka, I want to run this:

INSERT INTO testRn(RowNum,ColA,ColB)
EXEC TestProc @OrderCol = 'B'

And testRn.RowNum there becomes this:

Maybe I should create a temporary table with an identity field, insert it there, and then use Identity field as a row number?

CodePudding user response：

Maybe something like that

declare @testRn table(RowNum int identity(1,1),  ColA varchar(50), ColB  varchar(50))

insert into @testRn
EXEC TestProc @OrderCol = 'A'

select * from @testRn

-- final table
insert testRn select * from testRn

if you need persist the result then some id should be saved with the result set, maybe some internal counter or uniqueidentifier

CodePudding user response：

In two steps you can INSERT your stored procedure data into testRn, then UPDATE testRn with a incremental row number using a user-defined variable:

INSERT INTO testRn(ColA,ColB)
EXEC TestProc @OrderCol = 'B';

DECLARE @id INT 
SET @id = 0 
UPDATE testRn
SET @id = RowNum = @id   1;

Result:

| RowNum | ColA | ColB  |
|--------|------|-------|
| 1      | yyy  | bbb   |
| 2      | aaa  | zzz   |

Fiddle here.