This is just a simple way of adding an ordinal column to a SQL result using a sub query.
This method depends on the Primary Key field of the table.
I used the
Northwind database for this example.
The idea is to select the Products with an ordinal numbered column...
| Ordinal |
ProductID |
ProductName |
CategoryID |
| 1 |
11 |
Queso Cabrales |
4 |
| 2 |
12 |
Queso Manchego La Pastora |
4 |
| 3 |
31 |
Gorgonzola Telino |
4 |
| 4 |
32 |
Mascarpone Fabioli |
4 |
| 5 |
33 |
Geitost |
4 |
To do this I've created the following stored procedure with the CategoryID is an optional parameter:
CREATE PROCEDURE [dbo].[sp_SelectProducts]
(
@CategoryID INT = NULL
)
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = N'
SELECT top 5 (SELECT COUNT(*)
FROM Products p
WHERE p.ProductID <= Products.ProductID '
IF (@CategoryID IS NOT NULL) BEGIN
SET @sql = @sql + N' AND p.CategoryID = Products.CategoryID '
END
SET @sql = @sql + N') AS Ordinal,
ProductID,
ProductName,
CategoryID,
QuantityPerUnit,
UnitPrice,
UnitsInStock,
UnitsOnOrder,
ReorderLevel
FROM Products
WHERE Discontinued != 1'
IF (@CategoryID IS NOT NULL) BEGIN
SET @sql = @sql + N' AND CategoryID = ' + CAST(@CategoryID AS NVARCHAR)
END
EXEC sp_executesql @sql;
For each product that is returned we get it's ordinal position by counting the ProductIDs already returned and it self.