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.
| Author |
Topic |
|
AbeHoffman
Starting Member
2 Posts |
 Posted - 2014-12-12 : 17:13:41
|
| Hello,I'm a bit new sql dev, but I can't seem to find the best way to ask this. My question is similar to:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=196462But varies just enough to be annoying. I've got a user defined table type called StringDictionaryTVP:CREATE TYPE [dbo].[StringDictionaryTVP] AS TABLE( [key] [varchar](500) NULL, [value] [varchar](500) NULL)Ideally I would like to be able to have a # of columns and directions in this table like so:DECLARE @OrderByClause StringDictionaryTVPINSERT INTO @OrderByClause([key], [value])values('gender','desc')INSERT INTO @OrderByClause([key], [value])values('name','asc')Since our database can be a bit sizable, I'd also like to use Common Table Expressions so I can page through them fairly easy.So my standard cte is something like this:---DECLARE @PageIndex INT = 0DECLARE @PageSize INT = 20;WITH results_cte AS ( SELECT U.*, ROW_NUMBER() over ( ORDER BY name ) RowNumfrom Users U)SELECT * FROM results_cteWHERE RowNum > @Offset AND RowNum <= @Offset + @PageSize--So where 'ORDER BY name' is I'd like to use the @OrderByClause in some sort of dynamic way. I've tried all kinds of stuff but even something like this doesn't get the actual column name I need;WITH results_cte AS ( SELECT U.*, ROW_NUMBER() over ( ORDER BY (select top 1 [key] +' '+ [value] from @OrderByClause) ) RowNumfrom Users U)===I may be chasing the wrong stick, but outside of dynamic sql, is something like this possible?Thanks,Abe |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
|
|
AbeHoffman
Starting Member
2 Posts |
|
|
|
|
|
|
|
Order by user defined table type