Order by with a columns of user defined table type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Order by with a columns of user defined table type

Author

Topic

k5_ce
Starting Member

3 Posts

Posted - 2014-09-28 : 09:40:49

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.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-28 : 17:40:53

The problem is that the order by clause uses a sub query that can return more than one value. Try


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

k5_ce
Starting Member

3 Posts

Posted - 2014-09-29 : 02:48:12

quote:
Originally posted by gbritton

The problem is that the order by clause uses a sub query that can return more than one value. Try

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

Thanks a million. You solved my problem.
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?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-29 : 09:41:18

sure. This should do it:


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

Note that I replaced the subqueries with a simple join. This should also be faster

k5_ce
Starting Member

3 Posts

Posted - 2014-09-29 : 11:41:17

quote:
Originally posted by gbritton

sure. This should do it:

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

Note that I replaced the subqueries with a simple join. This should also be faster

Thanks a million. You solved my big problem.
Best Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources