1

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:
enter image description here

Picture 2:
enter image description here

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
HelloWorld1
  • 13,688
  • 28
  • 82
  • 145

2 Answers2

1

Without an explicit ORDER BY Statement, SQL Server will determine the order using a variety of means e.g. collation\indexes\order of insert etc. This is arbitrary and will change over time!

No Seatbelt - Expecting Order without ORDER BY

If you want to guarantee the order of your output, you need to make it ordered! For example (order by insertion):

CREATE TYPE [tvf_id] AS TABLE
(
    [pk_id] [int] NOT NULL IDENTITY(1,1),
    [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
    ORDER BY b.pk_id
end
sarin
  • 5,227
  • 3
  • 34
  • 63
  • It will be working based on the sentence "The data in the UDT will be random for every database request"? – HelloWorld1 Jun 23 '15 at 09:05
  • I don't understand what value in "[pk_id]" will be added? – HelloWorld1 Jun 23 '15 at 09:05
  • [pk_id] is an integer column that auto increments by 1 from 1. i.e. the first value to be inserted is 1, the second 2 the third 3. etc.. It is giving you an increasing number that follows the order of your inserted numbers. This will guarantee the order when you select them out of the table. You don't need to do anything with it. It will automatically populate as you insert values into the Type. The naming isn't great. Feel free to change it! – sarin Jun 23 '15 at 09:07
0
CREATE PROCEDURE [sp_Test]  @pID tvf_id READONLY 
as
begin
    set nocount on
    ;with pIDList AS
    (
        Select id, RW= ROW_NUMBER() OVER( order by (Select NULL)) from @pID

    )
    SELECT 
        a.*
    FROM datatable a  inner join pIDList b on a.id = b.id
    order by RW

end
naser daneshi
  • 284
  • 1
  • 10
  • 1
    Interestingly, whilst this works, it may not be foolproof. See this Stack post: http://stackoverflow.com/questions/18961789/calculating-sql-server-row-number-over-for-a-derived-table – sarin Jun 23 '15 at 09:40