patternMinor

A Customized Table with Chronological order

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

ordercustomizedwithchronologicaltable

Problem

Goal:

My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.

Problem:

When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8.

I don't know how to do it?

Information:

I'm using SQL server 2012.

The data in the UDT will be random for every database request.

Picture 1:

Picture 2:

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)                        
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'), 
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'), 
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'), 
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
)
GO

CREATE PROCEDURE [sp_Test]
    @pID tvf_id READONLY
as
begin
    set nocount on
    SELECT a.*
    FROM datatable a inner join @pID b on a.id = b.id
end

GO

--------------------------------------------------------

DECLARE @data tvf_id INSERT INTO @data([id]) 
VALUES (8), (2), (4), (1), (3);

exec sp_Test @pID = @data

Solution

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
    ,[OrdCol] [INT] NOT NULL    
)
GO

CREATE PROCEDURE [sp_Test]
    @pID tvf_id READONLY
as
begin
    set nocount on
    SELECT a.*
    FROM datatable a inner join @pID b on a.id = b.id
    ORDER BY b.OrdCol ASC 
end

GO

--------------------------------------------------------

DECLARE @data tvf_id INSERT INTO @data([id],[OrdCol]) 
VALUES (8,1), (2,2), (4,3), (1,4), (3,5);

exec sp_Test @pID = @data

Code Snippets

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
    ,[OrdCol] [INT] NOT NULL    
)
GO



CREATE PROCEDURE [sp_Test]
    @pID tvf_id READONLY
as
begin
    set nocount on
    SELECT a.*
    FROM datatable a inner join @pID b on a.id = b.id
    ORDER BY b.OrdCol ASC 
end

GO

--------------------------------------------------------

DECLARE @data tvf_id INSERT INTO @data([id],[OrdCol]) 
VALUES (8,1), (2,2), (4,3), (1,4), (3,5);

exec sp_Test @pID = @data

Context

StackExchange Database Administrators Q#104849, answer score: 2

Revisions (0)

No revisions yet.