How to properly reverse an order of a row in MSSQL Stored Procedure?-CodePudding

I do have this MSSQL stored procedure, that creates a table using a csvString and reverse the order of the rows of it. But in my case It did not reverse the order of my rows. Below is my stored procedure, with comments included such as the incorrect results and results I want.

Stored Procedure:

ALTER PROCEDURE [dbo].[MY_STOREDPROC]
 @csv_string NVARCHAR(MAX),
    @separator NCHAR(1)= '#'
AS
BEGIN
    -- Declare a table variable to hold the csv rows
    DECLARE @csv_table TABLE (row NVARCHAR(MAX))

     --Split the csv string into rows and insert them into the table variable
    INSERT INTO @csv_table
    SELECT value
    FROM STRING_SPLIT(@csv_string, @separator)
    WHERE LEN(value) > 0

    -- Print the rows in the table variable
    SELECT * FROM @csv_table

--RESULT:
--       row
-----------------------
--Mark,Fernandez,Rodriguez,Male,25
--Anne,Poe,Rodriguez,Male,30
--Zed,Smith,Garcia,Female,17
--Miya,Marksman,Gold,Female,25


    -- Print the rows returned by the stored procedure in reversed order(supposedly but it does not)
     SELECT row 
    FROM (
        SELECT row, ROW_NUMBER() OVER (ORDER BY row DESC) AS row_number
        FROM @csv_table
    ) AS t
    ORDER BY row DESC

--  RESULT:
--      row
-----------------------
--Zed,Smith,Garcia,Female,17
--Miya,Marksman,Gold,Female,25                                              
--Mark,Fernandez,Rodriguez,Male,25                                                                
--Anne,Poe,Rodriguez,Male,30


--RESULT I WANT 
--row
-----------------------
--Miya,Marksman,Gold,Female,25
--Zed,Smith,Garcia,Female,17
--Anne,Poe,Rodriguez,Male,30
--Mark,Fernandez,Rodriguez,Male,25

END

CodePudding user response：

It looks like you want the values in the reverse order that they appear in @csv_string, although since you've not included the starting value for this, that is a bit of a guess.

If I have guessed correctly, then in SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance you can specify that you want the ordinal when using string split, and retain the original position in the string, so you can get your desired result with something like this:

DECLARE @csv_string NVARCHAR(MAX) = 'Mark,Fernandez,Rodriguez,Male,25#Anne,Poe,Rodriguez,Male,30#Zed,Smith,Garcia,Female,17#Miya,Marksman,Gold,Female,25',
    @separator NCHAR(1)= '#'

DECLARE @csv_table TABLE (Position INT, row NVARCHAR(MAX))
INSERT @csv_table(Position, row)
SELECT  ss.ordinal, ss.value
FROM    STRING_SPLIT(@csv_string, @separator, 1) AS ss

SELECT  ct.row
FROM    @csv_table AS ct
ORDER BY ct.Position;

SELECT ct.row
FROM @csv_table AS ct
ORDER BY ct.Position DESC;

In versions that don't support ordinal then you can (ab)use OPEN_JSON to split your string, which gives you the ordinal in the key column:

DECLARE @csv_string NVARCHAR(MAX) = 'Mark,Fernandez,Rodriguez,Male,25#Anne,Poe,Rodriguez,Male,30#Zed,Smith,Garcia,Female,17#Miya,Marksman,Gold,Female,25',
    @separator NCHAR(1)= '#'

DECLARE @csv_table TABLE (Position INT, row NVARCHAR(MAX))
INSERT @csv_table(Position, row)
SELECT  oj.[Key], oj.Value
FROM    OPENJSON(CONCAT('["', REPLACE(@csv_string, @separator, '","'), '"]')) AS oj;

SELECT  ct.row
FROM    @csv_table AS ct
ORDER BY ct.Position;

SELECT ct.row
FROM @csv_table AS ct
ORDER BY ct.Position DESC;

Examples on db<>fiddle