Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Order by with a columns of user defined table type


  • Please log in to reply
2 replies to this topic

#1 k5_ce

k5_ce

  • Members
  • 2 posts
  • OFFLINE
  •  
  • Local time:08:53 PM

Posted 28 September 2014 - 08:49 AM

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:

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:
 

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.



BC AdBot (Login to Remove)

 


m

#2 Billy O'Neal

Billy O'Neal

    Visual C++ STL Maintainer


  • Malware Response Team
  • 12,301 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Redmond, Washington
  • Local time:10:23 AM

Posted 28 September 2014 - 04:51 PM

The ORDER BY operator accepts a column name, not a data set. That is, you want something like:

 

SELECT id, value FROM mytable

WHERE id = 1234

ORDER BY value

 

You can't order by a set of values -- an order is an order of the result set which you don't have when you execute that query.

 

If you for some reason have a set of values and you want the IDs for those values returned in value order (which would be awfully strange), create a temporary table and LEFT JOIN it with the result of the query you described here.

 

Billy3


Twitter - My statements do not establish the official position of Microsoft Corporation, and are my own personal opinion. (But you already knew that, right?)
Posted Image

#3 k5_ce

k5_ce
  • Topic Starter

  • Members
  • 2 posts
  • OFFLINE
  •  
  • Local time:08:53 PM

Posted 29 September 2014 - 02:28 AM

The ORDER BY operator accepts a column name, not a data set. That is, you want something like:

 

SELECT id, value FROM mytable

WHERE id = 1234

ORDER BY value

 

You can't order by a set of values -- an order is an order of the result set which you don't have when you execute that query.

 

If you for some reason have a set of values and you want the IDs for those values returned in value order (which would be awfully strange), create a temporary table and LEFT JOIN it with the result of the query you described here.

 

Billy3

 

Thanks for your answer. Yes, you are right. But, I was searching for a solution without needing to create a table.

I discovered the answer. I should use ORDER BY like this:
minus.gif

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?






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users