sql server - inserting rank column in SP table-parameter

Question

My problem is similar to this one: how to maintain order while doing join in sql

Basically I have a table parameter in a stored procedure that contains some IDs in a particular order. I want to use those IDs in an inner join and return results based on the order of the input table-parameter. So, I must find a way to build a new table that contains a rank together with the IDs.

From what I see on similar posts, there is no way to achieve this in SQL, unless I also feed some extra rank column from the outside non-SQL world (inside my table parameter)?

This seems SO alien to me... Is that really the situation?

UPDATE:

(Obviously, I can't use ROW_NUMBER() or RANK() since those require sorting by a column and I only have IDs in my table)

Basically, my input table is in this form:

CREATE TYPE [dbo].[IdTable] AS TABLE(
    [Id] [int] NOT NULL
)

And it is fed as parameter to my SP.

Answer 1

I have tried using IDENTITY on a column, but it seems that the DataTable class that I used didn't allow inserting rows with less columns than the number of columns the table had (so that I could not omit the IDENTITY column when inserting data and let it auto-increment).

What I did instead, was providing the rank column values from C# code and order by that column in my SP.

Not the best solution that I could imagine, but at least it works.

Answer 2

Sounds like what you are after is a sub-query with a ranking function:

https://msdn.microsoft.com/en-us/library/ms189798.aspx

If you build your sub-query with a column generated from e.g. ROW_NUMBER(), and alias that sub-query and join to it, you can then order your query results by that column.

Answer 3

1. Add identity seed enabled column to your input table now, column will be automatically populated with physical order they are inserted into.
2. Use this column to sort after join

Answer 4

I think u can use a new table variable in the structure of your input table parameter and a new column for the rank/rownumber (perhaps identity column). Then u just have to fil lthe table variable like

INSERT INTO @tableVar(...) SELECT (...) FROM @inputTableParameter

The you can do everything with the new table variable and the rownumber/rank column.