Order by with a columns of user defined table type

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hello.
I have a user defined table type with two columns: ID: int, Value: float.
Also, I have a table with different columns.
I have a stored procedure.

SQL

ALTER PROCEDURE [dbo].[MyProcedure]
  @List AS dbo.MyUserDefinedTableType READONLY
AS
BEGIN
  SET NOCOUNT ON;

    SELECT  *
FROM    mytable
WHERE   id IN (  SELECT ID FROM @List )
END

I want to add "order by Value" to this stored procedure. Like below:

SQL

ALTER PROCEDURE [dbo].[MyProcedure]
  @List AS dbo.MyUserDefinedTableType READONLY
AS
BEGIN
  SET NOCOUNT ON;

    SELECT  *
FROM    mytable
WHERE   id IN (SELECT ID FROM @List )
ORDER BY (SELECT Value FROM @List )
END

But this way is not true, and I get error when i debug my application.
More information: I fill this user defined table type in c# with data of a DataTable.

Please help me. It gets on my nerves. Thanks a lot.

*******************************************************************
More details about what I want to do:

I have a Grid View. I want to fill my grid view with a query like below:

SQL

select * from mytable
 where id = ID s in mylist 
order by Value s in mylist.

My codes:

C#

 protected void btnfind_Click(object sender, EventArgs e)
{
        List<KeyValuePair<int, double>> candidatelist = CalculateScores();
        FinalMatch(candidatelist);
 }

protected void FinalMatch(List<KeyValuePair<int, double>> finallist)
    {
        DataTable tvp = new DataTable();
        tvp = ConvertToDatatable(finallist);
        try
        {
            SqlDataAdapter sda = new SqlDataAdapter("tblspecifications", ClsDataBase.con);
            sda.SelectCommand.CommandType = CommandType.StoredProcedure;
            sda.SelectCommand.CommandText = "dbo.DoSomethingWithCandidates";
            SqlParameter tvparam = sda.SelectCommand.Parameters.AddWithValue("@List", tvp);
            tvparam.SqlDbType = SqlDbType.Structured;
            ds.Clear();
            sda.Fill(ds, "tblspecifications");
            if (ds.Tables["tblspecifications"].Rows.Count != 0)
                {
                    this.gv = ds.Tables["tblspecifications"];
                    this.gv.DataBind();
                 }         
        }
        catch (SqlException ex)
        {

            Response.Write("<script language='javascript'>alert(\"" + ex.ToString() + "\")</script>");
        }
        catch (Exception ex)
        {
            Response.Write("<script language='javascript'>alert(\"" + ex.ToString() + "\")</script>");

        }
        finally
        {
            ClsDataBase.con.Close();
        }
    }

protected DataTable ConvertToDatatable(List<KeyValuePair<int, double>> finallist)
{
    DataTable dt = new DataTable();
    dt.Columns.Add("ID");
    dt.Columns.Add("Score");
    foreach (var item in finallist)
    {
        var row = dt.NewRow();
        row["ID"] = item.Key;
        row["Score"] = item.Value;
        dt.Rows.Add(row);
    }
    return dt;
}

And my codes in Sql are:

SQL

CREATE TYPE dbo.CandidateList
AS TABLE
(
  ID INT,
   Score FLOAT
);
GO

CREATE PROCEDURE dbo.DoSomethingWithCandidates
   @List AS dbo.CandidateList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT  * FROM    tblspecifications
WHERE   id IN (  SELECT ID FROM @List ) 
END
GO

With above codes, I have a Grid View filling with query like below:

SQL

select * from mytable
 where id = ID s in mylist

I want to complete what I did by adding this:

SQL

order by Value s in mylist.

I don`t know what to do for this part. I tried different ways but no result.
Please help me. Thanks so much.

Posted 28-Sep-14 3:30am

k5_ce

Updated 28-Sep-14 6:21am

v2

Add a Solution

Comments

Tiwari Avinash 29-Sep-14 5:36am

Why don't you use Join for selection i.e. in select statement and then try order by i think will work fine. Sorry I haven't checked this. please do let me know if tit worked.

k5_ce 1-Oct-14 9:07am

Thanks for your answer.
Yes, You are right. I have tried Joins in different ways without results, but I could discover the true way. I edited my solution (solution2).
Best Regards.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

k5_ce · Accepted Answer · 2014-09-28T21:19:00

Thanks for your attention.
Finally, I discovered the answer.
I should use ORDER BY like this:

SQL

ORDER BY (SELECT top(1) Value from @list li where mytable.id = li.id)

Just one more question:
Is it possible to select this Value and have this column next to columns of my table? For example, Imagine we have:
*****************************************
Data in my user defined table type:
ID / Value
4 / 30
1 / 20
3 / 10
*****************************************
Data in my table:
id / name
1 / a
2 / b
3 / c
4 / d
*****************************************
The out put with this query will be:
id / name
4 / d
1 / a
3 / c
*****************************************
I sit possible to have this out put?
id / name / Value
4 / d / 30
1 / a / 20
3 / c / 10
*****************************************
As I know, if my user defined table type was a table, I could Right Join this with my table to achieve this. But, I don`t want to create a table. Is it possible?

*******************************************************************************
Answer:

Yes,It is possible. For solving first and second question, we need to write this query:

SQL

select my.id, my.name, li.value
from mytable my
join @list li
on my.id = li.id
order by li.value

In my codes, I should alter my procedure in this way:

SQL

ALTER PROCEDURE [dbo].[DoSomethingWithCandidates]
  @List AS dbo.CandidateList READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT  mytable.*,  li.Score
   FROM    tblspecifications mytable
   join @List li
   on mytable.id = li.id
ORDER BY li.Score desc
END

Good Luck.

Kornfeld Eliyahu Peter · Accepted Answer · 2014-09-28T03:40:00

Solution 1

SQL

ORDER BY (SELECT Value FROM @List )

This can only work if the inner select returns a single value, otherwise it is an error of 'Subquery returned more than 1 value.'...
Please read about ORDER BY[^]...

Posted 28-Sep-14 3:40am

Kornfeld Eliyahu Peter

Comments

k5_ce 28-Sep-14 10:02am

Yes, You are right. I know this is wrong for the reason you say.I said this for clearing what I want to do.
I don`t know what else should I do. Another way I tried was joining mytable and MyUserDefinedTableType, like below:

SELECT mytable.*
FROM mytable as mytable
inner join sys.table_types as mylist
on mytable.id= mylist.type_table_object_id
WHERE (mylist.name = 'dbo.MyUserDefinedTableType') AND (id IN ( SELECT ID FROM @List ))

But it is again wrong. I don`t know what else should I do

Kornfeld Eliyahu Peter 28-Sep-14 11:52am

To help you further you have to explain what do you try to do - from your samples it's unclear...

k5_ce 28-Sep-14 12:22pm

OK, Thanks. I added more details in my question.
Thanks for your attention.

Kornfeld Eliyahu Peter 28-Sep-14 12:26pm

If I put into words, you want to retrieve those records where id is in an external list and order the result set by those items in the external list...
In my read it means to order by id!!!

k5_ce 29-Sep-14 3:21am

Thanks for your attention. But, I want to order by Value,not id.

Maciej Los 1-Oct-14 11:37am

Good point, +5!